select
date_trunc('week',from_unixitme(xwhen/1000)) as cycle,
count(1) as pv
from
event_vd
where
ds between 20200101 and 20200630
group by
1
order by
1
select
*
from
event_vd
where
ds between 20200101 and 20200630
and date_format(from_unixtime(xwhen/1000),‘%H’) = ‘08’
常用标记符
标记符
说明
%Y
年份，4位数字
%y
年份，2位数字
%m
月份(01-12)
%c
月份(1-12)
%d
日(00-31)
%e
日(0-31)
%H
小时(00-23)
%k
小时(0-23)
%i
分钟(00-59)
%s
秒(00-50)
%T
时分秒(等同于%H:%i:%s)
%w
星期几(0-6,周日为一周第一天)
也支持通过extract函数直接从日期类型属性中提取数据
按年，月查看历史以来订单次数
select
extract(year from from_unixtime(xwhen/1000)) as year,
extract(month from from_unixtime(xwhen/1000)) as month,
count(1)
from
event_vd
where
xwhat='%order%'
group by
1,2
order by
1,2
SELECT
EVENT .*
FROM
event_vd event,
profile_vd PROFILE
WHERE
EVENT .distinct_id = PROFILE .distinct_id
AND EVENT .ds = 20200701
AND PROFILE .xwho = '001'
ORDER BY
EVENT .xwhen
SELECT
CASE
WHEN order_cnts < 5 THEN
'[0-5)'
WHEN order_cnts < 10 THEN
'[5-10)'
WHEN order_cnts < 20 THEN
'[10-20)'
WHEN order_cnts < 50 THEN
'[20-50)'
ELSE
'[50-∞)'
END,
count(1)
FROM
(
SELECT
distinct_id,
count(1) AS order_cnts
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat = 'order'
GROUP BY
1
)
GROUP BY
1
ORDER BY
1
查询做过A事件但是未做过B事件的用户
通过left join计算2个事件活跃用户的差集
SELECT
a.distinct_id
FROM
(
SELECT DISTINCT
distinct_id
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat = 'A'
) a
LEFT JOIN (
SELECT DISTINCT
distinct_id
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat = 'B'
) b ON a.distinct_id = b.distinct_id
WHERE
b.distinct_is IS NULL
通过计算不同事件的活跃次数，再进行过滤
SELECT
distinct_id
FROM
(
SELECT
distinct_id,
COUNT_if (xwhat = 'A') AS A_CNTS,
COUNT_if (xwhat = 'B') AS B_CNTS
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat IN ('A', 'B')
GROUP BY
1
)
WHERE
a_cnts >= 1
AND b_cnts = 0
SELECT
distinct_id,
sum(next_xwhen - xwhen)
FROM
(
SELECT
distinct_id,
xwhen,
lead (xwhen) over (
PARTITION BY distinct_id,
"$session_id"
ORDER BY
xwhen
) AS next_xwhen
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND "$session_id" IS NOT NULL
)
GROUP BY
1
SELECT
distinct_id,
sum(
CASE
WHEN next_xwhen - xwhen > 1800000 THEN
0
ELSE
next_xwhen - xwhen
END
)
FROM
(
SELECT
distinct_id,
xwhen,
lead (xwhen) over (
PARTITION BY distinct_id
ORDER BY
xwhen
) AS next_xwhen
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND "$session_id" IS NOT NULL
)
GROUP BY
1
SELECT
distinct_id,
xwhen,
next_xwhen - xwhen
FROM
(
SELECT
distinct_id,
xwhen,
xwhat,
lead (xwhen) over (
PARTITION BY distinct_id
ORDER BY
xwhen
) AS next_xwhen,
lead (xwhat) over (
PARTITION BY distinct_id
ORDER BY
xwhen
) AS next_xwhat
FROM
event_vd
WHERE
ds BETWEEN 20200701
AND 20200731
AND xwhat IN ('A', 'B')
AND "$session_id" IS NOT NULL
)
WHERE
xwhat = 'A'
AND next_xwhat = 'B'
AND next_xwhen - xwhen <= 60000
