Блог

Я хочу получить результат запроса, в котором каждый столбец имеет значения, показывающие, сколько раз объект встречался за последние 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 дней?

используйте выражение прецедента

 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
  

Я бы рекомендовал использовать COUNTIF() в BigQuery:

 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 можно ссылаться как на функцию (т.Е. с помощью () ), круглые скобки необязательны, и я не вижу никакого смысла в их использовании.

Кроме того, если вы измеряете периоды времени в днях, возможно, вам не захочется включать компонент time. Если это так, вам следует задать другой вопрос.