sql—如何获取2020年1月前两次访问总共产生500美元以上的所有访客ID的列表？

1dkrff03 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(372)

table：
会议
会话\n
访客id
垂直的
会话\u id
交易
会话\n
会话\u id
rev\铲斗
收入
当前有以下查询（使用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美元。开放的任何反馈和简化查询。谢谢！

sql Join window-functions group-by sqlite

来源：https://stackoverflow.com/questions/61859645/how-to-pull-a-list-of-all-visitor-ids-that-generated-more-than-500-combined-in

1条答案

按热度按时间

w7t8yxp51#

可以使用窗口函数和聚合：

select visitor_id, sum(t.revenue) total_revenue
from (
    select 
        s.visitor_id, 
        t.revenue, 
        row_number() over(partition by s.visitor_id order by t.session_ts) rn
    from transactions t
    inner join sessions s on s.session_id = t.session_id
    where t.session_ts >= '2020-01-01' and t.session_ts < '2020-02-01'
) t
where rn <= 2
group by visitor_id
having sum(t.revenue) >= 500

子查询连接两个表，按目标月份过滤（注意，使用半开放间隔 predicate 比在日期列上应用日期函数更有效），并在同一客户的访问组中对每一行进行排序。
然后，外部查询过滤每个访问者的前两次访问，按访问者聚合，计算相应的收入，并使用 having 条款。

赞(0）回复(0）举报 2021-08-09

我来回答

sql—如何获取2020年1月前两次访问总共产生500美元以上的所有访客ID的列表？

1条答案

相关问题

热门标签

最新问答