2016年10月5日水曜日

【検討中コード】

---

WITH mysite_with AS(
  SELECT
    MIN(TD_TIME_FORMAT(time,
        'yyyy-MM-dd',
        'JST')) AS date_time,
    imid
  FROM
    log_db.log_mysite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-07-01',
      'JST')
    AND td_browser != 'Mobile Safari'
  GROUP BY
    imid
),
mysite_access_day_with AS(
  SELECT
    TD_TIME_FORMAT(time,
      'yyyy-MM-dd',
      'JST') AS access_day,
    imid
  FROM
    log_db.log_mysite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-10-01',
      'JST')
    AND td_browser != 'Mobile Safari'
  GROUP BY
    TD_TIME_FORMAT(time,
      'yyyy-MM-dd',
      'JST'),
    imid
),
othersite_with AS(
  SELECT
    MIN(TD_TIME_FORMAT(time,
        'yyyy-MM-dd HH:mm:ss',
        'JST')) AS date_time,
    imid
  FROM
    log_db.log_othersite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-10-01',
      'JST')
    AND td_browser != 'Mobile Safari'
    AND brand = 'brand/ie'-- ブランド名を指定
  GROUP BY
    imid
) SELECT
  date_diff(
    'day',
    CAST(
      mysite_with.date_time AS TIMESTAMP
    ),
    CAST(
      next_access.access_day AS TIMESTAMP
    )
  ) AS time_diff,
  COUNT(DISTINCT mysite_with.imid) AS uu
FROM (mysite_with)
JOIN
  (othersite_with)
  ON (
    --他社サイトのブランドサイト訪問
    mysite_with.imid = othersite_with.imid
  )
JOIN
  (
    -- アクセス初日後、最初のアクセス日を抽出
    SELECT
      MIN(TD_TIME_FORMAT(TD_TIME_PARSE(mysite_access_day_with.access_day),
          'yyyy-MM-dd',
          'jst')) AS access_day,
      mysite_access_day_with.imid
    FROM (mysite_access_day_with)
    JOIN
      (mysite_with)
      ON (
        mysite_access_day_with.access_day != mysite_with.date_time
--修正
  AND
        mysite_access_day_with.imid = mysite_with.imid
      )
    GROUP BY
      mysite_access_day_with.imid
  )next_access
  ON (
    mysite_with.imid = next_access.imid
  )
GROUP BY
  date_diff(
    'day',
    CAST(
      mysite_with.date_time AS TIMESTAMP
    ),
    CAST(
      next_access.access_day AS TIMESTAMP
    )
  )
ORDER BY
  time_diff

---

0 件のコメント :

コメントを投稿