关于重复字段的条件逻辑
关于重复字段的条件逻辑
提问于 2017-11-08 15:25:45
假设我有一个用以下代码创建的表结构：
WITH YourTable AS (
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'm' as class, 'u1' AS userid, 4 as score UNION ALL
SELECT 'a1' AS item, TIMEsTAMP('2016-03-03 19:53:23 UTC') AS click_time, 'm' as class, 'u2' AS userid, 1 as score UNION ALL
SELECT 'a1' AS item, TIMEsTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'd' as class, 'u3' AS userid, 0 as score UNION ALL
SELECT 'a1' AS item, TIMEsTAMP('2016-03-03 19:51:23 UTC') AS click_time, 'd' as class, 'u4' AS userid, 8 as score UNION ALL
SELECT 'a2' AS item, TIMEsTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'd' as class, 'u1' AS userid, 5 as score UNION ALL
SELECT 'a2' AS item, TIMEsTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'm' as class, 'u2' AS userid, 2 as score
)
SELECT
item,
ARRAY_AGG(STRUCT(click_time, userid, class, score)) as clicks
FROM
YourTable
GROUP BY
item现在，我想选择类“m”单击的第一个事件的时间戳(在一个项目中)。目前，我发现没有子查询就能做到这一点。
你知不知道这是否可能？
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-11-08 16:35:04
BigQuery标准SQL
#standardSQL
WITH YourTable AS (
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'm' AS class, 'u1' AS userid, 4 AS score UNION ALL
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:53:23 UTC') AS click_time, 'm' AS class, 'u2' AS userid, 1 AS score UNION ALL
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'd' AS class, 'u3' AS userid, 0 AS score UNION ALL
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:51:23 UTC') AS click_time, 'd' AS class, 'u4' AS userid, 8 AS score UNION ALL
SELECT 'a2' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'd' AS class, 'u1' AS userid, 5 AS score UNION ALL
SELECT 'a2' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'm' AS class, 'u2' AS userid, 2 AS score
)
SELECT item,
ARRAY_AGG(STRUCT(click_time, userid, class, score) ORDER BY CASE class WHEN 'm' THEN 0 ELSE 1 END, click_time LIMIT 1) AS clicks
FROM YourTable
GROUP BY item结果如下
item clicks.click_time clicks.userid clicks.class clicks.score
a1 2016-03-03 19:52:23 UTC u1 m 4
a2 2016-03-03 19:52:23 UTC u2 m 2 或
#standardSQL
WITH YourTable AS (
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'm' AS class, 'u1' AS userid, 4 AS score UNION ALL
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:53:23 UTC') AS click_time, 'm' AS class, 'u2' AS userid, 1 AS score UNION ALL
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'd' AS class, 'u3' AS userid, 0 AS score UNION ALL
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:51:23 UTC') AS click_time, 'd' AS class, 'u4' AS userid, 8 AS score UNION ALL
SELECT 'a2' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'd' AS class, 'u1' AS userid, 5 AS score UNION ALL
SELECT 'a2' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'm' AS class, 'u2' AS userid, 2 AS score
),
TransformedTable AS (
SELECT item,
ARRAY_AGG(STRUCT(click_time, userid, class, score)) AS clicks
FROM YourTable
GROUP BY item
)
SELECT item,
( SELECT click
FROM UNNEST(clicks) click
WHERE class = 'm'
ORDER BY click_time
LIMIT 1
) AS clicks
FROM TransformedTable 具有与第一个查询完全相同的输出。
Stack Overflow用户
发布于 2017-11-08 15:39:53
我不知道你为什么要避免子查询。在这种情况下，需要对数组进行标量子查询，并对每一行进行计算。尝试这样做，使用示例数据和模式作为基础：
WITH YourTable AS (
SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'm' as class, 'u1' AS userid, 4 as score UNION ALL
SELECT 'a1' AS item, TIMEsTAMP('2016-03-03 19:53:23 UTC') AS click_time, 'm' as class, 'u2' AS userid, 1 as score UNION ALL
SELECT 'a1' AS item, TIMEsTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'd' as class, 'u3' AS userid, 0 as score UNION ALL
SELECT 'a1' AS item, TIMEsTAMP('2016-03-03 19:51:23 UTC') AS click_time, 'd' as class, 'u4' AS userid, 8 as score UNION ALL
SELECT 'a2' AS item, TIMEsTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'd' as class, 'u1' AS userid, 5 as score UNION ALL
SELECT 'a2' AS item, TIMEsTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'm' as class, 'u2' AS userid, 2 as score
), TransformedTable AS (
SELECT
item,
ARRAY_AGG(STRUCT(click_time, userid, class, score)) as clicks
FROM
YourTable
GROUP BY
item
)
SELECT
item,
(SELECT click_time FROM UNNEST(clicks)
WHERE class = 'm'
ORDER BY click_time LIMIT 1
) AS first_click_time
FROM TransformedTable;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47183504
复制相关文章
相似问题
腾讯云开发者
