时间戳

首页 > 解决方案 > 在 BigQuery 中，您将如何使用两个日期列计算每月和每日总计？

sql - 在 BigQuery 中，您将如何使用两个日期列计算每月和每日总计？

问题描述

假设我有这个简单的查询（两个日期列是 TIMESTAMP）：

SELECT
Song_Name, Artist_Name, Album_Name, Genre, Sub_Genres, Song_Length_Seconds, On_Platform_DateTime, Off_Platform_DateTime
FROM Music_Platform.Music_Data

以及将导致的表的前 4 行：

表格截图

使用两个日期列，我希望能够对所有歌曲可用的每个月和日（在 TIMESTAMP 列中的两个日期之间）求和“Song_Length_Seconds”。

例如（使用屏幕截图），仅查看前 4 行且仅在 2020-06 月份，这将仅包括前 3 行，总计 739.8 秒。

我最初是从生成一个临时月份表开始的，但我不确定如何继续，或者这是否是最好的方法：

with
  months as (
    select format_date('%Y-%m', month_start) month_key
    from unnest(
      generate_date_array('2020-01-01', '2022-12-01', interval 1 month)
    ) month_start
  )

标签： sqlgoogle-bigquery

解决方案

WITH table AS (
    SELECT 242.4 Song_Length_Seconds, TIMESTAMP '2020-06-05 11:00:00 UTC' On_Platform_DateTime
    union all
    SELECT 240.6 Song_Length_Seconds, TIMESTAMP '2020-06-17 05:00:00 UTC' On_Platform_DateTime
    UNION ALL
    SELECT 256.8 Song_Length_Seconds, TIMESTAMP '2020-06-24 05:00:00 UTC' On_Platform_DateTime
    UNION ALL
    SELECT 380.4 Song_Length_Seconds, TIMESTAMP '2020-07-21 05:00:00 UTC' On_Platform_DateTime
)
SELECT
    EXTRACT(YEAR FROM On_Platform_DateTime) year
    , EXTRACT(MONTH FROM On_Platform_DateTime) month
    , SUM(Song_Length_Seconds) Sum_Song_Length_Seconds
FROM
    table
GROUP BY
    year
    , month

推荐阅读