表格:
- 届会
- session_ts
- visitor_id
- 垂直
- session_id
- 交易次数
- session_ts
- session_id
- rev_bucket
- 收入
当前具有以下查询(使用SQLite):
SELECT
s.visitor_id,
sub.session_id,
month,
year,
total_rev,
CASE
WHEN (row_num IN (1,2) >= total_rev >= 500) THEN 'Yes'
ELSE 'No' END AS High_Value_Transactions,
sub.row_num
FROM
sessions s
JOIN
(
SELECT
s.visitor_id,
t.session_id,
strftime('%m',t.session_ts) as month,
strftime('%Y',t.session_ts) as year,
SUM(t.revenue) as total_rev,
row_number() OVER(PARTITION BY s.visitor_id ORDER BY s.session_ts) as row_num
FROM
Transactions t
JOIN
sessions s
ON
s.session_id = t.session_id
WHERE strftime('%m',t.session_ts) = '01'
AND strftime('%Y',t.session_ts) = '2020'
GROUP BY 1,2
) sub
ON
s.session_id = sub.session_id
WHERE sub.row_num IN (1,2)
ORDER BY 1
我在确定前两个会话的总费用为500美元时遇到了麻烦。 公开接受任何反馈并简化查询。谢谢!
您可以使用窗口函数和聚合:
子查询将两个表连接起来,在目标月份上进行过滤(请注意,使用半开放间隔谓词比在date列上应用日期函数更有效),并在同一位客户的访问组中对每一行进行排名。
Then, the outer query filters on the first two visits per visitor, aggregates by visitor, computes the corresponding revenue, and filters it with a
having
clause.