CODEWITHSUNDEEP
google-bigquery
Phuc Trinh
Phuc Trinh

Reputation: 455

BIGQUERY - HOW TO USE SUM FUNCTION BY SPECIFIC DATE?

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

Upvotes: 1

Views: 665

Answers (2)

Nathan Nasser
Nathan Nasser

Reputation: 1004

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

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.

Upvotes: 2

Related Questions