CODEWITHSUNDEEP
google-bigquery
rbkk2016
rbkk2016

Reputation: 215

bigquery multiple where caluse

I have to perform the below logic my output table should have following fields for each of the userid. userid, prodid, version, last used time - I will use MAX(starttime), calculation1 which will be prodname=RX*10 for last 1 day,calculation2 which will be prodname=RX*10 for last 7 days

for the columns calculation1 and calculation2, I can write individual select query and provide the time period in where clause and then join but what is the more effecient way to do this in a single query?

SELECT userid, prodID, Version, 
MAX(StartTime) AS Last_Used_TimeStamp, 
FROM  
[ProdTable] 
WHERE NativeProd ='AAA' 
AND GlobalPod='AAA'
GROUP BY 1,2,3,4 

SELECT userid, COUNT(prodname)*10 AS calculation1 
from [prodtable] 
where prod ='AAA' 
AND Date(startTime) = CURRENT_DATE() -1

Upvotes: 1

Views: 1989

Answers (1)

Haipeng Su
Haipeng Su

Reputation: 2541

There are some confusions in the query, make sure the names are consistent in both queries. In your second query, I assume that your count is group by userid. In this situation, you can use a SUM(CASE ..) to achieve both calculations in one query. 

SELECT userid, 
       SUM(CASE WHEN StartTime >= DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY') THEN 1 ELSE 0 END) AS calculation1,
       SUM(CASE WHEN StartTime >= DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY') THEN 1 ELSE 0 END) AS calculation2
FROM [ProdTable]
WHERE prod = 'AAA'
GROUP BY userid

Also, If you want to integrate the process of getting Last_Used_TimeStamp query with the calculation query, I suppose we can use PARTITION BY function to achieve the goal.

Upvotes: 2

Related Questions