sql - 雪花窗函数 last_value 和 max
问题描述
我有一张这样的桌子:
我想为每个 user_key 获取每个日期/月/工作日/周的 top total_listened
我想我需要使用窗口函数我可以得到不同的日期格式:
MONTH(stream_date) for months
WEEKDAY(stream_date) for weekday
WEEK(stream_date) for week
我试过这个:
select
MAX(vals.total_listened_per_day) as RECORD_STREAM_DAY_TIME,
MAX(vals.total_listened_per_month) as RECORD_STREAM_MONTH_TIME,
MAX(vals.total_listened_per_week) as RECORD_STREAM_WEEK_TIME,
MAX(vals.most_active_weekday) as MOST_ACTIVE_WEEKDAY_TIME
last_value(days.date) over (partition by user_key order by days.total_listened) as RECORD_STREAMDAY,
from
(
select user_key, stream_date as date,
sum(st.length_listened) over (partition by user_key, stream_date) as total_listened_per_day,
sum(st.length_listened) over (partition by user_key, MONTH(stream_date)) as total_listened_per_month,
sum(st.length_listened) over (partition by user_key, WEEK(stream_date)) as total_listened_per_week,
sum(st.length_listened) over (partition by user_key, DAYNAME(stream_date)) as most_active_weekday
group by 1,2
.....
)
用于获取金额(以_TIME为结尾的变量),但不适用于获取特定的日期/月份....(末尾没有_TIME的变量,例如RECORD_STREAMDAY),这是因为组by ,它按stream_date而不是month(stream_date)分组,例如,我不知道如果没有doin子查询我怎么能做到
解决方案
我认为你想要的逻辑是:
select user_key,
max(total_listened_per_day ) as max_total_listened_per_day
max(total_listened_per_week ) as max_total_listened_per_week,
max(total_listened_per_month) as max_total_listened_per_month,
max(case when rn_day = 1 then date_trunc('day', stream_date) end) as most_active_day,
max(case when rn_week = 1 then date_trunc('week', stream_date) end) as most_active_week,
max(case when rn_month = 1 then date_trunc('month', stream_date) end) as most_active_month
from (
select t.*,
rank() over(partition by user_key order by total_listened_per_day desc) as rn_day,
rank() over(partition by user_key order by total_listened_per_week desc) as rn_week,
rank() over(partition by user_key order by total_listened_per_month desc) as rn_month
from (
select t.*
sum(st.length_listened) over (partition by user_key, date_trunc('day', stream_date)) as total_listened_per_day,
sum(st.length_listened) over (partition by user_key, date_trunc('week', stream_date)) as total_listened_per_week
sum(st.length_listened) over (partition by user_key, date_trunc('month', stream_date)) as total_listened_per_month
from mytable t
) t
) t
group by user_key
最内部的子查询计算每天、每周和每月收听时间的窗口总和。下一个子查询使用该信息对记录进行排名。最后,外部查询使用条件聚合来带来相应的持续时间和周期。如果有关系,则选择最近的时期。
推荐阅读
- spring - Spring JPA 一对多关系在更新时返回 null
- python - python语音识别中的AttributeError
- python-3.x - 我的数据在代码窗口的 Dataframe 中位于 3 列中,但在导出到 csv 时位于 1 列中
- c++ - SFINAE on Error in Dependent Type causes unexpected hard error
- javascript - input to only accept alpha chars (no numbers, or special)
- list - Vim - jump back to previous position when moving multiple lines at once
- javascript - how to show text to another div id in another page in javascript?
- python - 如何在打印项目之前计算 CSV 中的行数?
- bash - 批处理作业提交失败:请求的节点配置不可用 SLURM
- javascript - Javascript popup that shows up when user tries to leave page won't close with button