如何在SQL中计算近两个月窗口内出现频率最高的水果？

现有表结构 #

table user
user_id | month_year | fruits  
------------------------------
1       | 2021-01    | apple
1       | 2021-01    | melon
1       | 2021-01    | orange
1       | 2021-02    | grape
1       | 2021-02    | orange
1       | 2021-02    | kiwi
1       | 2021-03    | grape
1       | 2021-03    | pear
1       | 2021-03    | banana
1       | 2021-04    | orange
1       | 2021-04    | kiwi
1       | 2021-04    | banana
1       | 2021-05    | grape
1       | 2021-05    | pear
1       | 2021-05    | kiwi

期望结果 #

user     | month_year |            fruits            |  two_months_most_freq
-------------------------------------------------------------------------
1        | 2021-01    | apple, melon, orange         | orange
1        | 2021-02    | grape, orange, kiwi          | orange
1        | 2021-03    | grape, pear, banana          | grape
1        | 2021-04    | orange, kiwi, banana         | banana
1        | 2021-05    | grape, pear, kiwi            | kiwi

需求说明 #

最后一列需返回过去两个月时间窗口内出现频率最高的水果，即当前月份和上一个月份中重复次数最多的水果。注意第一行应返回orange，因为当没有上一个月份时，仅使用当前月份窗口。

现有代码（计算全量数据最高频水果） #

select * from (
  select user_id, year_month, 
    string_agg(distinct fruit) as fruits
  from user
  group by  user_id, year_month
) join (
  select user_id, fruit
  from user
  group by user_id, fruit
  qualify 1 = row_number() over(partition by user_id order by count(*) desc)
)
using (user_id)   

解决方案 #

要适配近两个月的时间窗口，需要为每个用户的每个月份划定对应的时间范围（当前月及上月），然后在该范围内统计水果频率。以下是适配后的SQL代码：

-- 第一步：生成每个用户每月的水果聚合列表
with monthly_fruits as (
    select 
        user_id,
        month_year,
        string_agg(distinct fruits, ', ') as fruits
    from "user"
    group by user_id, month_year
),
-- 第二步：为每个用户的每个月份，关联其近两个月的所有水果数据
window_fruits as (
    select 
        mf.user_id,
        mf.month_year,
        u.fruits as fruit,
        -- 计算当前窗口内每个水果的出现次数
        count(*) over (partition by mf.user_id, mf.month_year, u.fruits) as freq
    from monthly_fruits mf
    left join "user" u 
        on u.user_id = mf.user_id
        -- 匹配当前月和上月的数据，处理字符串格式的月份
        and u.month_year between 
            to_char(add_months(to_date(mf.month_year, 'YYYY-MM'), -1), 'YYYY-MM')
            and mf.month_year
),
-- 第三步：为每个月份窗口选出频率最高的水果
top_fruit_per_window as (
    select 
        user_id,
        month_year,
        fruit as two_months_most_freq
    from window_fruits
    qualify row_number() over (
        partition by user_id, month_year 
        order by freq desc, fruit asc -- 频率相同时按水果名称排序，保证结果唯一
    ) = 1
)
-- 最后关联得到最终结果
select 
    mf.user_id as user,
    mf.month_year,
    mf.fruits,
    tf.two_months_most_freq
from monthly_fruits mf
join top_fruit_per_window tf 
    on mf.user_id = tf.user_id 
    and mf.month_year = tf.month_year
order by mf.month_year;

代码说明 #

• monthly_fruits：延续原代码逻辑，聚合每个用户每月的水果列表。
• window_fruits：通过自连接关联当前月及上月的所有水果数据，同时计算每个水果在窗口内的出现次数。这里处理了字符串格式的月份转换，确保能正确匹配上月数据。
• top_fruit_per_window：使用qualify和row_number()按用户、月份分组，选出频率最高的水果；若存在频率相同的情况，按水果名称排序取第一个（可根据需求调整排序规则）。
• 最后将月度水果列表与窗口最高频水果关联，得到符合需求的结果。

内容的提问来源于stack exchange，提问作者user19927421