使用array_agg和struct后如何在列中查找值? [英] How to find a value in a column after I've used array_agg and struct?
问题描述
在我的数据库中使用以下代码后,得到以下结果:
After using the following code in my database, I got the following results:
select USERID,
array_agg(struct(ORDER_TIME, DELIVERY_TIME, PLATFORM) order by ORDER_TIME) STATS
from `project.dataset.table`
group by USERID
having count(1) > 1
order by USERID
用户 ID | STATS.ORDER_TIME | STATS.DELIVERY_TIME | 统计平台 |
---|---|---|---|
011 | 2021-01-09 11:14:18 | 2021-01-09 11:44:01 | 手机 |
2021-02-12 16:15:51 | 2021-02-12 17:16:51 | 桌面 | |
2021-03-30 17:23:45 | 2021-02-12 17:16:51 | 桌面 | |
033 | 2021-01-01 12:30:14 | 2021-01-01 13:30:00 | 手机 |
2021-04-16 23:00:45 | 2021-04-16 23:45:40 | 手机 | |
040 | 2021-02-18 19:22:55 | 2021-02-18 20:00:05 | 手机 |
2021-05-06 09:12:13 | 2021-05-06 10:00:10 | 桌面 |
但是,我只需要那些同时包含移动设备和桌面设备的寄存器.所以我需要这样的东西,结果是没有 USERID 040 的数据,因为他们只在手机上订购:
However, I only need those registers which contains both mobile AND desktop. So I need something like this, a result where there is no data for the USERID 040, because they only ordered on a mobile phone:
用户 ID | STATS.ORDER_TIME | STATS.DELIVERY_TIME | 统计平台 |
---|---|---|---|
011 | 2021-01-09 11:14:18 | 2021-01-09 11:44:01 | 手机 |
2021-02-12 16:15:51 | 2021-02-12 17:16:51 | 桌面 | |
2021-03-30 17:23:45 | 2021-02-12 17:16:51 | 桌面 | |
033 | 2021-01-01 12:30:14 | 2021-01-01 13:30:00 | 手机 |
2021-05-06 09:12:13 | 2021-05-06 10:00:10 | 桌面 |
我怎么可能这样做?非常感谢!
How can I possibly do that? Thank you very much!
推荐答案
最简单"的方法是在 have
子句
The "simplest" way is to add few more conditions into having
clause
select USERID, array_agg(struct(ORDER_TIME, DELIVERY_TIME, PLATFORM) order by ORDER_TIME) STATS
from `project.dataset.table`
group by USERID
having count(1) > 1
and 'mobile' in unnest(array_agg(PLATFORM))
and 'desktop' in unnest(array_agg(PLATFORM))
order by USERID
如果应用于您问题中的样本数据 - 输出为
if applied to sample data in your question - output is
如果您有更多此类条目要进行比较 - 您可以使用以下版本以避免重复类似的代码行
In cases when you have more such entries to compare with - you can use below version to avoid repeating similar line of code
select USERID, array_agg(struct(ORDER_TIME, DELIVERY_TIME, PLATFORM) order by ORDER_TIME) STATS
from `project.dataset.table`
group by USERID
having count(1) > 1
and array_length(array_agg(distinct if(PLATFORM in ('mobile', 'desktop'), PLATFORM, null))) = 2
order by USERID
这篇关于使用array_agg和struct后如何在列中查找值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!