时间戳

首页 > 解决方案 > BigQuery - 查询每个并设置列中的元素

sql - BigQuery - 查询每个并设置列中的元素

问题描述

我想循环查询几个元素。

这是查询：

SELECT
  timestamp_trunc(timestamp, DAY) as Day,
  count(1) as Number
FROM `table`
WHERE user_id="12345" AND timestamp >= '2021-07-05 00:00:00 UTC' AND timestamp <= '2021-07-08 23:59:59 UTC'
GROUP BY 1
ORDER BY Day

所以我在两个日期之间每天为用户“12345”一个行计数器，这是完美的。

但我想对我的表的每个 user_id 进行此查询，如果可能的话，我希望每一天都在列上，所以每一行都是一个用户，每列可用的数字（即一天）。

想要的结果：

User   | 2021-07-05 | 2021-07-06 | 2021-07-07
---------------------------------------------
user_1 | 345        | 16         | 41
user_2 | 555        | 53         | 26

非常感谢你

标签： sqlgoogle-bigquery

解决方案

使用以下方法

SELECT * FROM (
  SELECT
    user_id,
    DATE(timestamp) as Day,
    COUNT(1) as Number
  FROM `project.dataset.table`
  WHERE timestamp >= '2021-07-05 00:00:00 UTC' AND timestamp <= '2021-07-08 23:59:59 UTC'
  GROUP BY 1, 2
)
PIVOT (SUM(Number) FOR Day IN ('2021-07-05','2021-07-06','2021-07-07'))

甚至更简单（没有原始查询中的 GROUP BY）

SELECT * FROM (
  SELECT
    user_id,
    DATE(timestamp) as Day,
  FROM `project.dataset.table`
  WHERE timestamp >= '2021-07-05 00:00:00 UTC' AND timestamp <= '2021-07-08 23:59:59 UTC'
)
PIVOT (COUNT(*) FOR Day IN ('2021-07-05','2021-07-06','2021-07-07'))

推荐阅读