时间戳

首页 > 解决方案 > 如何在“出租车”和“图书”类别中选择第一个获得 1000 积分的 1000 位顾客？(SQLite)

sql - 如何在“出租车”和“图书”类别中选择第一个获得 1000 积分的 1000 位顾客？(SQLite)

问题描述

BONUS表有属性：client_id、bonus_date、累积奖金的数量（bonus_cnt）、添加奖金的交易的mcc代码（mcc_code）。MCC_CATEGORIES 表是一个 mcc 代码参考。属性：mcc-code（mcc_code），类别（例如，超市，运输，药店等，mcc_category）

如何在“出租车”和“图书”类别中选择第一个获得 1000 积分的 1000 位顾客？

奖金表如下所示：

CLIENT_ID  BONUS_DATE  BONUS_CNT  MCC_CODE
1121       2020-01-02  23         5432
3421       2020-04-15  7          654
...

MCC_CATEGORIES 表如下所示：

MCC_CODE   MCC_CATEGORY
5432       Taxi
3532       Music
...

标签： sqlsqlitesuminner-joinwhere-clause

解决方案

我会使用窗口函数和聚合：首先加入表格并计算每个用户和类别的奖金的运行总和。然后按用户和类别聚合，得到他们达到奖金 1000 的日期。最后，计算每个用户在两个类别上达到目标的日期，按此排序，并限制：

select client_id, max(bonus_date) bonus_date
from (
    select client_id, mcc_category, min(bonus_date) bonus_date
    from (
        select b.client_id, b.bonus_date, c.mcc_category, 
            sum(bonus_cnt) over(partition by b.client_id, c.mcc_category order by b.bonus_date) sum_bonus
        from bonus b
        inner join mcc_categories c on c.mcc_code = b.mcc_code
        where mcc_category in ('Taxi', 'Books')
    ) t
    where sum_bonus >= 1000
    group by client_id, mcc_category
) t
group by client_id
having count(*) = 2
order by bonus_date
limit 1000

SQLite 从 3.25 版开始提供窗口函数。

推荐阅读