date - 以不同的时间跨度分组
问题描述
目前我正在努力实现一些有点重叠的聚合。
我表的当前结构是:
|ymd |id|costs|
|--------|--|-----|
|20200101|a |10 |
|20200102|a |12 |
|20200101|b |13 |
|20200101|c |15 |
|20200102|c |1 |
但是,我想以每个项目具有不同时间跨度的方式对其进行分组。考虑到我在 20200103 上运行此查询,我想要达到的结果是:
| timespan | id | costs |
|------------|----|-------|
| last 2 days| a | 22 |
| last 1 day | a | 12 |
| last 2 days| b | 13 |
| last 1 day | b | 0 |
| last 2 days| c | 16 |
| last 1 day | c | 1 |
我已经尝试了很多事情,但到目前为止我无法实现我所需要的。这是我尝试过的查询,没有正确的结果:
SELECT
CASE
WHEN ymd BETWEEN date_add(current_date(),-2) AND to_date(current_date()) THEN '2 days'
WHEN ymd BETWEEN date_add(current_date(),-1) AND to_date(current_date()) THEN '1 day'
END AS timespan,
id,
sum(costs) AS costs
FROM `table`
GROUP BY
CASE
WHEN ymd BETWEEN date_add(current_date(),-2) AND to_date(current_date()) THEN '2 days'
WHEN ymd BETWEEN date_add(current_date(),-1) AND to_date(current_date()) THEN '1 day'
END,
id
解决方案
您可以构建一个存储时间戳的派生表,将其与不同用户列表交叉连接以生成所有可能的组合,然后将表与 aleft join
和聚合一起使用:
select d.timespan, i.id, coalesce(sum(t.costs), 0) costs
from (select distinct id from mytable) i
cross join (
select 1 n, 'last 1 day' timespan
union all select 2, 'last 2 day'
) d
left join mytable t
on t.ymd between date_add(current_date(), - d.n) and current_date()
group by d.n, d.timespan, i.id
推荐阅读
- flutter - 如何使用嵌套字典 dart 中的键路径获取值
- excel - 有没有办法创建一个具有唯一名称的动态表,将其按月分隔?
- ms-access - 从表中选择值,其中值以逗号分隔的字符串
- flutter - 如何在 Flutter 中使用 Razorpay 订单 API?
- python - 折叠子路径
- python - 可变默认参数 - (为什么)我的代码很危险?
- networking - “ifconfig eth0 down”或“ip link set eth0 down”模拟拔掉网线的方法是否正确
- r - 如何将每个 ID 保存在数据帧中,一旦出现平局,则选择最大值拳头,以防平局随机
- c# - 将枚举与列表传递给视图具有巨大的性能差异
- angular - 如何在 Angular 7 中创建自定义库时在 CSS 文件中公开资产(图标/图像)url 路径