时间戳

首页 > 解决方案 > SQL 对不同日期范围的行求和

sql - SQL 对不同日期范围的行求和

问题描述

我有一个 google bigquery 表，它捕获所有用户的日常使用数据，每个用户都有自己的Anchor_date（当这个用户第一次开始时）。Usage记录在每个User上Date。

为了跟踪每个订阅期的使用情况，我想总结锚定日期之间的所有使用情况（因此，如果锚定日期最初对2014-01-23约翰来说，那么它最近的（当前月份是 2021-05）订阅期应该2021-04-23最多2021-05-22, 用法为 32 + ....+ 24. 然后下一个循环将是2021-05-23up to2021-06-22等等。

+------------+-------------+------------+---------------------+
| date       |   User      | Usage      | Anchor_date         |
+------------+-------------+------------+---------------------+
| 2021-04-01 |    John     | 10.00      | 2014-01-23          |
+------------+-------------+------------+---------------------+
| 2021-04-02 |    John     | 15.00      | 2014-01-23          |
+------------+-------------+------------+---------------------+
| ...        |    ...      | ...        | 2014-01-23          |
+------------+-------------+------------+---------------------+
| 2021-04-23 |    John     | 32.00      | 2014-01-23          |
+------------+-------------+------------+---------------------+
| ...        |    ...      | ...        | 2014-01-23          |
+------------+-------------+------------+---------------------+
| 2021-05-22 |    John     | 24.00      | 2014-01-23          |
+------------+-------------+------------+---------------------+
| ...        |    ...      | ...        | 2014-01-23          |
+------------+-------------+------------+---------------------+
| 2021-04-02 |    Kevin    | 28.00      | 2015-04-02          |
+------------+-------------+------------+---------------------+
| ...        |    ...      | ...        | 2015-04-02          |
+------------+-------------+------------+---------------------+
| 2021-05-01 |    Kevin    | 32.00      | 2015-04-02          |
+------------+-------------+------------+---------------------+

我的目标是让每个用户在最近的订阅期内发生的总使用量。所以决赛桌应该是这样的。

+-------------+------------------+-------------+-----------+
|   User      | Aggregated_usage | Start_date  | End_date  |
+-------------+------------------+-------------+-----------+
|    John     | 32 + ... + 24    | 2021-04-23  | 2021-05-22|
+-------------+------------------+-------------+-----------+ 
|    Kevin    | 28 + ... + 32    | 2021-04-02  | 2021-05-01|
+-------------+------------------+-------------+-----------+
| ...         |    ...           | ...         | ...       |
+-------------+------------------+-- ----------+-----------+

到目前为止，我只知道如何获取最近订阅期的开始日期和结束日期。但是，这会为无效日期（例如“2021-04-31”）引发错误。

date(extract(year from current_date()), extract(month from date_sub(current_date(), interval 30 day)), extract(day from anchor_date)) as start_date, 
date(extract(year from current_date()), extract(month from current_date()), extract(day from date_sub(anchor_date, interval 1 day))) as end_date

通过这些不同的日期范围进行求和似乎非常复杂，所以我不确定这是否可能。

感谢所有帮助！谢谢。

标签： sqlgoogle-bigquerysumdate-range

解决方案

WITH anchorAndEndDate AS (
SELECT user
   ,SUM(Usage) AS aggregated_usage
   ,MIN(Anchor_date) as anchor_date
   ,MAX(Date) as end_date
FROM your_table
GROUP BY 1)
SELECT
   user
   ,aggregated_usage
   ,DATE_ADD(DATE_ADD(DATE_TRUNC(end_date, MONTH), INTERVAL -1 MONTH), INTERVAL EXTRACT(DAY FROM anchor_date)-1 DAY) AS start_date 
   ,end_date
FROM anchorAndEndDate

请注意，在编写 SQL 以将列命名为 snake_case 或 camelCase 时，这是最佳做法。

推荐阅读