BIGQUERY -如何使用特定日期的SUM函数？

Question

所以基本上，我想通过"2018-09-07" and "2018-09-13"之间的累积日期来计算EST_VIEWS的总和。我该怎么做呢？

Row CMS_ID  VIDEO_ID    CHANNEL_ID  TITLE   EST_VIEWS   DATE     
1   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    1   2018-09-10   
2   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    3   2018-09-07   
3   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    1   2018-09-09   
4   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    3   2018-09-11   
5   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    2   2018-09-13   
6   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    1   2018-09-12   
7   2   V133h8Rn8W0 UC-CcF1XGCRASDRrEOI_zEnQ    Michael Jackson Nhí - Châu Phát Luân Ngôi Sao Nhí Trại Hè Trường Anh Ngữ RES 2018   1   2018-09-11   
8   2   V133h8Rn8W0 UC-CcF1XGCRASDRrEOI_zEnQ    Michael Jackson Nhí - Châu Phát Luân Ngôi Sao Nhí Trại Hè Trường Anh Ngữ RES 2018   1   2018-09-12   

结果是：

Row CMS_ID  VIDEO_ID    CHANNEL_ID  TITLE   DATE    total    
1   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    2018-09-10  1    
2   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    2018-09-11  3    
3   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    2018-09-07  3    
4   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    2018-09-09  1    
5   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    2018-09-12  1    
6   2   WKqQSws7CRM UC-CcF1XGCRASDRrEOI_zEnQ    Con Két Quậy    2018-09-13  2    

Answer 1

由于您希望看到给定范围内的所有日期，即使您的源表可能没有这些日期的任何条目，我们也可以在这种情况下使用日历表。假设您想要的范围很小，以下内容应该就足够了：

WITH dates AS (
    SELECT DATE("2018-09-07") AS DATE UNION ALL
    SELECT DATE("2018-09-08") UNION ALL
    SELECT DATE("2018-09-09") UNION ALL
    SELECT DATE("2018-09-10") UNION ALL
    SELECT DATE("2018-09-11") UNION ALL
    SELECT DATE("2018-09-12") UNION ALL
    SELECT DATE("2018-09-13")
)

SELECT
    t1.DATE,
    SUM(t2.EST_VIEWS) OVER (ORDER BY t1.DATE) AS total
FROM dates t1
LEFT JOIN yourTable t2
    ON t1.DATE = t2.DATE
GROUP BY
    t1.DATE;

请注意，如果您需要更大范围的日期，那么您可能需要一种更干净、更自动化的方式来生成日程表。对于某些选项，请使用See this SO question。

Answer 2

我想我明白了，你想知道一个VIDEO_ID在'2018-09-07‘和'2018-09-13’之间有多少视图，如果我错了，请纠正我。因此，如果VIDEO_ID在2018-09-07和2018-09-08分别出现一次和两次，并且在本周剩下的时间都看不到，那么您希望总和为3。

您应该使用where子句：

SELECT 
   VIDEO_ID,
   SUM(EST_VIEWS)
WHERE
   DATE(DATE)
BETWEEN 
   DATE('2018-09-07') AND DATE('2018-09-13')
GROUP BY
   VIDEO_ID