---
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
---
注目の投稿
【kepler.gl】コロナ対策による人流の変化も地図上に可視化（各種メディアで報道）
kepler.glのサイト画面 kepler.glを使ってコロナ対策の効果を分析したところ、テレビ、新聞、ネットのメディアから問い合わせや報道依頼が殺到。今も、土日返上で都内や全国の人流変化を分析しています。この記事では人流変化の可視化に便利なkepler.glにつ...
0 件のコメント :
コメントを投稿
注: コメントを投稿できるのは、このブログのメンバーだけです。