Bigquery - 交叉连接的替代方案 [英] Bigquery - alternative for cross join
问题描述
除了我在查询中为每个用户标识在过去7天中查询
count(nativeprodID)指定的内容之外,还必须在我的输出中有下列列
在过去15天内每个用户ID的(nativeProdID)数量
过去30天内每个用户ID的(nativeprodID)数量
$ b $ p
SELECT userid,nativeprodID,nativeVersion,nativeVersionName,
MAX(StartTime)AS Last_Used_TimeStamp,
FROM
[ProdTable]
WHERE NativeProd = 'AAA'
AND GlobalPod ='AAA'
GROUP BY 1,2,3,4
限额10
我正在考虑使用交叉连接,但还有其他更简单的技巧吗? 尝试使用交叉连接
SELECT
userid,
nativeprodID,
nativeVersion,
nativeVersionName,
MAX(StartTime)AS Last_Used_TimeStamp,
SUM(IF(DATE(StartTime)> DATE(DATE_ADD(CURRENT_DATE(),-7,DA Y)),1,0))AS count_last_7_days,
SUM(IF(DATE(StartTime)> DATE(DATE_ADD(CURRENT_DATE(),-15,DAY)),1,0))AS count_last_15_days,
SUM(IF(DATE(StartTime)> DATE(DATE_ADD(CURRENT_DATE DAY)),1,0))AS count_last_30_days,
FROM [ProdTable]
WHERE NativeProd ='AAA'AND GlobalPod ='AAA'
GROUP BY 1,2,3, 4
限额10
I have to have below columns in my output in addition to the ones that I have specified in the query
count of (nativeprodID) for each userID in last 7 days
count of (nativeProdID) for each userID in last 15 days
count of (nativeprodID) for each userID in last 30 days
SELECT userid, nativeprodID, nativeVersion, nativeVersionName,
MAX(StartTime) AS Last_Used_TimeStamp,
FROM
[ProdTable]
WHERE NativeProd ='AAA'
AND GlobalPod='AAA'
GROUP BY 1,2,3,4
LIMIT 10
I was thinking of using cross join but is there any other easier technique?
Try below
SELECT
userid,
nativeprodID,
nativeVersion,
nativeVersionName,
MAX(StartTime) AS Last_Used_TimeStamp,
SUM(IF(DATE(StartTime) > DATE(DATE_ADD(CURRENT_DATE() , -7, "DAY")), 1, 0)) AS count_last_7_days,
SUM(IF(DATE(StartTime) > DATE(DATE_ADD(CURRENT_DATE() , -15, "DAY")), 1, 0)) AS count_last_15_days,
SUM(IF(DATE(StartTime) > DATE(DATE_ADD(CURRENT_DATE() , -30, "DAY")), 1, 0)) AS count_last_30_days,
FROM [ProdTable]
WHERE NativeProd ='AAA' AND GlobalPod='AAA'
GROUP BY 1,2,3,4
LIMIT 10
这篇关于Bigquery - 交叉连接的替代方案的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!