如何在BigQuery中添加总行数的列

我在BigQuery中有一个表,用于保存DATESTAMP和温度传感器值。 我通常按​​日期过滤查询。 我的目标是在此查询中添加一列,以放置总行数。

例如,如果我运行此查询,我将得到以下结果:

SELECT DATESTAMP, Temperature
FROM
    `my_project.my_folder.my_table`
WHERE
    DATESTAMP BETWEEN TIMESTAMP("2020-02-05 00:00:00.000")
    AND TIMESTAMP("2020-02-06 00:00:00.00")

行日期温度

1 2020-02-05 06:44:37 UTC 15.14

2 2020-02-05 09:41:11 UTC 18.25

3 2020-02-05 12:11:25 UTC 21.21

4 2020-02-05 22:15:37 UTC 14.65

当我运行此查询时,我得到以下结果:

SELECT count(*) AS num_total
FROM
    `my_project.my_folder.my_table`
WHERE
    DATESTAMP BETWEEN TIMESTAMP("2020-02-05 00:00:00.000")
    AND TIMESTAMP("2020-02-06 00:00:00.00")

ROW num_total

1 4

我的目标是编写查询并接收此结果

ROW DATESTAMP温度num_total

1 2020-02-05 06:44:37 UTC 15.14 4

2 2020-02-05 09:41:11 UTC 18.25 4

3 2020-02-05 12:11:25 UTC 21.21 4

4 2020-02-05 22:15:37 UTC 14.65 4

我能怎么做?

评论
  • 说不出口的爱
    说不出口的爱 回复

    这是一种选择。

    SELECT DATESTAMP
           , Temperature
           , (select count(*) from `my_project.my_folder.my_table`) as num_total
    FROM `my_project.my_folder.my_table`
    WHERE DATESTAMP BETWEEN TIMESTAMP("2020-02-05 00:00:00.000")
          AND TIMESTAMP("2020-02-06 00:00:00.00")
    
  • 鸡蛋
    鸡蛋 回复

    您可以进行窗口计数:

    SELECT datestamp, Temperature, COUNT(*) OVER() num_total
    FROM `my_project.my_folder.my_table`
    WHERE 
        datestamp     >= TIMESTAMP("2020-02-05 00:00:00.000") 
        AND datestamp <  TIMESTAMP("2020-02-06 00:00:00.000")
    

    Note that I rewrote your date filter to use an half-open interval instead of between: likely, you do not want the upper bound to be inclusive in the date range.