在google bigquery中查询不同 [英] Query distinct in google bigquery
本文介绍了在google bigquery中查询不同的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
id,名称,键,日期
我有一张有四列的表格,看起来像这样: 1,'A','x1','2015-11-11'
2,'A','x1','2015-11-11'
3,'B','x2 ','2015-11-11'
4,'B','x2','2015-11-11'
5,'A','x1','2015-11-12 '
6,'A','x1','2015-11-12'
7,'B','x2','2015-11-12'
8,'' B','x2','2015-11-12'
9,'D','x3','2015-11-12'
10,'A','x1',' 2015-12-11'
11,'A','x1','2015-12-11'
12,'B','x2','2015-12-11'
13,'B','x2','2015-12-11'
14,'A','x1','2015-12-12'
15,'A', 'x1','2015-12-12'
16,'B','x2','2015-12-12'
17,'B','x2','2015-12 -12'
18,'D','x3','2015-12-12'
我想为每个新 键
-s计数不同的 code> date :
2015-11-11 2 - :x1和x2)
2015-11-12 1 - (一个新钥匙:x3)
2015-12-11 2 - (两个不同的钥匙:x1和x2) - (不同的月份11 )
2015-12-12 1 - (一个新密钥:x3) - (不同月份11)
每个月只显示一次。
我该如何做?
解决方案
它与您之前的问题完全相同 - 您只需按月添加额外的组/分区 - >使用YearMonth字段
SELECT DATE,EXACT_COUNT_DISTINCT(key)AS键
FROM(
SELECT DATE,key,LEAD(DATE)OVER(PARTITION BY KEY,YearMonth ORDER BY DATE DESC)as new
FROM(SELECT DATE,LEFT(DATE,7)AS YearMonth,key FROM YourTable GROUP BY 1,2,3)
)WHERE new IS NULL
GROUP BY DATE
ORDER BY DATE
I have a table with four columns, looking like this:
id,name, key, date
1,'A' ,'x1','2015-11-11'
2,'A' ,'x1','2015-11-11'
3,'B' ,'x2','2015-11-11'
4,'B' ,'x2','2015-11-11'
5,'A' ,'x1','2015-11-12'
6,'A' ,'x1','2015-11-12'
7,'B' ,'x2','2015-11-12'
8,'B' ,'x2','2015-11-12'
9,'D' ,'x3','2015-11-12'
10,'A' ,'x1','2015-12-11'
11,'A' ,'x1','2015-12-11'
12,'B' ,'x2','2015-12-11'
13,'B' ,'x2','2015-12-11'
14,'A' ,'x1','2015-12-12'
15,'A' ,'x1','2015-12-12'
16,'B' ,'x2','2015-12-12'
17,'B' ,'x2','2015-12-12'
18,'D' ,'x3','2015-12-12'
I want to count the number of distinct new key
-s for each date
:
2015-11-11 2 -- (two distinct keys: x1 and x2)
2015-11-12 1 -- (one new key: x3)
2015-12-11 2 -- (two distinct keys: x1 and x2) - (different month 11)
2015-12-12 1 -- (one new key: x3) - (different month 11)
Only distinct in each month.
How can I do this?
解决方案
it is exactly as in you previous question(s) - you just need to add extra group/partition by month --> see use of YearMonth field
SELECT DATE, EXACT_COUNT_DISTINCT(key) AS keys
FROM (
SELECT DATE, key, LEAD(DATE) OVER(PARTITION BY key, YearMonth ORDER BY DATE DESC) AS new
FROM (SELECT DATE, LEFT(DATE, 7) AS YearMonth, key FROM YourTable GROUP BY 1, 2, 3)
) WHERE new IS NULL
GROUP BY DATE
ORDER BY DATE
这篇关于在google bigquery中查询不同的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文