如何选择第一个获得 1000 奖励积分的客户购买类别"Taxi"和"Books"？(SQLite)

Question

BONUS表具有属性：client_id、BONUS_date、累计奖金数(BONUS_cnt(、为其添加奖金的交易的mcc代码(mcc_code(。MCC_CATEGORIES表是一个MCC代码参考。属性：mcc代码(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
...

Answer 1

我会使用窗口函数和聚合：首先连接表，计算每个用户和类别的奖金总额。然后按用户和类别进行聚合，得到他们达到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版本开始时可用。

Answer 2

如何选择1000名率先获得1000点奖励积分的客户进行分类购买"出租车"；以及"；书"？

我猜你想把这两个类别的奖金合并在一起。如果是：

select client_id, min(bonus_date) as min_bonus_date
from (select b.client_id, b.bonus_date, b.bonus_cnt,
sum(b.bonus_cnt) over (partition by b.client_id order by b.bonus_date) as running_bonus_cnt
from bonus b join
mcc_categories c
on c.mcc_code = b.mcc_code
where mcc_category in ('Taxi', 'Books')
) bc
where running_bonus_cnt >= 1000 and
running_bonus_cnt - bonus_cnt < 1000
group by client_id
order by min_bonus_date
limit 1000;

请注意这是如何工作的。子查询计算正在运行的奖金金额。where子句然后获得奖金计数首先超过1000的一行。

剩下的只是聚合。