BIGQUERY - HOW TO USE SUM FUNCTION BY SPECIFIC DATE?

Question

So basically, i want to caculate SUM of EST_VIEWS by DATE which is cumulative between "2018-09-07" and "2018-09-13". How can i do it?

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

The result:

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

Tim Biegeleisen · Answer 1 · 2018-09-18T07:16:14.420

2

Since you want to see all dates in the given range, even when your source table may not have any entries for those dates, we can use a calendar table in this case. Given that your desired range is small, the following should suffice:

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;

Note that if you needed a larger range of dates, then you would probably a want a cleaner, more automated way of generating a calendar table. See this SO question for some options.

edited Sep 18 '18 at 07:16

answered Sep 18 '18 at 04:58

Tim Biegeleisen

502,043
27
286
360

I tried your way. But i still didn't get the expected result `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 ` – Phuc Trinh Sep 18 '18 at 06:11
it is hard to see here, i will put the result on the post – Phuc Trinh Sep 18 '18 at 06:12
Do you want to report all dates, even those dates which don't appear in your table? – Tim Biegeleisen Sep 18 '18 at 06:16
yes, i want to report all dates, i mean i want to have a table which is calculated `SUM` EST_VIEWS by each VIDEO_ID (cumulative between '2018-09-07' AND '2018-09-13') – Phuc Trinh Sep 18 '18 at 06:22
@PhucTrinh I updated my answer to use a calendar table. – Tim Biegeleisen Sep 18 '18 at 06:32
I tried your way again, but it is still not cumulative :( – Phuc Trinh Sep 18 '18 at 06:54
I explain the cumulative in the comment above. – Phuc Trinh Sep 18 '18 at 07:03
Please help me, I mentioned about calculating cumulative in the comment, but seems like you dont see it. Now i just edit in the question. Please help me this issue. Sorry for not giving more specific at first; – Phuc Trinh Sep 18 '18 at 07:07
@PhucTrinh Try using `SUM(t2.EST_VIEWS) OVER (ORDER BY t1.DATE)`. – Tim Biegeleisen Sep 18 '18 at 07:16

score 0 · Answer 2 · answered Nov 24 '18 at 04:20

I think I understand, you want to have how many view a VIDEO_ID has had between '2018-09-07' AND '2018-09-13', correct me if I am wrong. So if a VIDEO_ID was seen once on 2018-09-07 and twice on 2018-09-08 and not seen for the rest of the week, you want to have the sum which is 3.

You should use a where clause:

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

BIGQUERY - HOW TO USE SUM FUNCTION BY SPECIFIC DATE?

2 Answers2