BigQuery的标准SQL-在过去的1、7和30天内获得数量

Question

我想有一个查询结果，每列的值显示了最后1、7和30天实体发生的次数。

我有：

文档：

+-----+---------+-------------------------+
| dId | score   | datetime                |
+-----+---------+-------------------------+
| A   | 100     | 2019-03-08 16:17:34.043 |
| B   | 80      | 2019-02-15 16:17:34.043 |
| C   | 70      | 2019-03-08 16:17:34.043 |
+-----+---------+-------------------------+

实体：

+------+-----+
| name | dId |
+------+-----+
| e1   |   A |
| e2   |   A |
| e1   |   B |
| e1   |   C |
| e2   |   C |
+------+-----+

预期输出：

+------+----+----+------+
| name | 1D | 7D |  30D |
+------+----+----+-------
| e1   | 2  |  2 |   3  |
| e2   | 1  |  1 |   2  |
+------+----+----+------+

从最近30天获取记录的一个简单查询是：

SELECT * FROM document where datetime >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 MONTH)

但是，我该如何在1,7,30天内加入并获得记录的计数？

Answer 1

用例表达

SELECT e.name,
SUM(CASE WHEN d.datetime>=DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 DAY)
                  THEN 1 ELSE 0 END) AS  oneD,
SUM(CASE WHEN d.datetime>=DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 7 DAY)
                  THEN 1 ELSE 0 END) AS sevenD ,
SUM(CASE WHEN d.datetime>=DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 30 DAY)
                  THEN 1 ELSE 0 END) AS thirtyD
FROM
document d JOIN entity e ON d.did=e.did GROUP BY e.name

Answer 2

我建议在bigquery中使用 COUNTIF()：

SELECT e.name,
       COUNTIF(d.datetime >= DATETIME_SUB(CURRENT_DATETIME, INTERVAL 1 day)) AS day_1,
       COUNTIF(d.datetime >= DATETIME_SUB(CURRENT_DATETIME, INTERVAL 7 day)) AS day_7,
       COUNTIF(d.datetime >= DATETIME_SUB(CURRENT_DATETIME, INTERVAL 30 day)) AS day_30
FROM document d JOIN
     entity e
     ON d.did = e.did
GROUP BY e.name;

尽管current_datetime可以称为函数（即使用()），但括号是可选的，我在使用它们时看不到任何值。

另外，如果您按照天数来测量时间段，则可能不想包括时间组成部分。如果是这样，您应该提出另一个问题。