分组连续记录
这是我的记录(输入)。所述field2
总是开始于100后,它可以采取以上100分组连续记录
field1 field2
===============
val1 100
val2 110
------------
val3 100
val4 110
val3 130
val3 140
------------
val1 100
任何值我需要组连续记录与100开始后,它可以是任何数量不是100对于上面的示例我需要的输出
field 1 field2
===================================================
(val1, val2) (100, 110)
(val3, val4, val3, val3) (100, 110, 130, 140)
(val1) (100)
如何做到这一点
让我假设你有一个指定排序的列。然后,你可以通过每个记录之前计数的“100”的记录数,然后使用array_agg()
鉴定组:
select array_agg(field1 order by id) as field1s,
array_agg(field2 order by id) as field2s
from (select t.*,
sum(case when field2 = 100 then 1 else 0 end) over (order by id) as grp
from t
) t
group by grp;
注:在MySQL解决方案看起来非常,非常不同。但是,它仍然会以select
开头。
大概我很幼稚。我想知道我将要使用的订单字段应该是什么。如问题('input')中给出的那样,这些是我在运行select查询时获取记录的顺序,并且我想根据该顺序对记录进行分组。想知道这是否可能(不使用订单栏)? – Raj
@Raj。 。 。它是*你的*数据。我不知道要使用哪个字段。但如果你没有一个,那么你不能做你想要的。你的逻辑需要对记录进行排序。 –
您必须有一些可用于定义订单的字段。
在下面的例子,我认为它是id
场
下面应该做你期望
#standardSQL
SELECT
CONCAT('(',STRING_AGG(field1 ORDER BY id), ')') AS field1,
CONCAT('(',STRING_AGG(CAST(field2 AS STRING) ORDER BY id), ')') AS field2
FROM (
SELECT
id, field1, field2,
COUNTIF(field2 = 100) OVER (ORDER BY id) AS grp
FROM yourTable
) t
GROUP BY grp
ORDER BY MIN(id)
您可以测试/与尝试下面的伪数据从你的问题是什么
#standardSQL
WITH yourTable AS (
SELECT 1 AS id, 'val1' AS field1, 100 AS field2 UNION ALL
SELECT 2 AS id, 'val2' AS field1, 110 AS field2 UNION ALL
SELECT 3 AS id, 'val3' AS field1, 100 AS field2 UNION ALL
SELECT 4 AS id, 'val4' AS field1, 110 AS field2 UNION ALL
SELECT 5 AS id, 'val3' AS field1, 130 AS field2 UNION ALL
SELECT 6 AS id, 'val3' AS field1, 140 AS field2 UNION ALL
SELECT 7 AS id, 'val1' AS field1, 100 AS field2
)
SELECT
CONCAT('(',STRING_AGG(field1 ORDER BY id), ')') AS field1,
CONCAT('(',STRING_AGG(CAST(field2 AS STRING) ORDER BY id), ')') AS field2
FROM (
SELECT
id, field1, field2,
COUNTIF(field2 = 100) OVER (ORDER BY id) AS grp
FROM yourTable
)
GROUP BY grp
ORDER BY MIN(id)
输出为
field1 field2
------ ------
(val1,val2) (100,110)
(val3,val4,val3,val3) (100,110,130,140)
(val1) (100)
想知道如果这是可能的(不使用订单栏)?
如果你真的有只在您的表的两个领域 - 你最有可能的运气了,需要重新审视与附加字段填入本表的逻辑被用于订购(作为时间轴)
作为一个完全最后的手段 - 你可以尝试例子飞产生这样栏下方 - 但请理解也绝对在得到命令,你希望
#standardSQL
WITH yourTable AS (
SELECT 'val1' AS field1, 100 AS field2 UNION ALL
SELECT 'val2' AS field1, 110 AS field2 UNION ALL
SELECT 'val3' AS field1, 100 AS field2 UNION ALL
SELECT 'val4' AS field1, 110 AS field2 UNION ALL
SELECT 'val3' AS field1, 130 AS field2 UNION ALL
SELECT 'val3' AS field1, 140 AS field2 UNION ALL
SELECT 'val1' AS field1, 100 AS field2
),
tempTable AS (
SELECT field1, field2, ROW_NUMBER() OVER() AS id
FROM yourTable
)
SELECT
CONCAT('(',STRING_AGG(field1 ORDER BY id), ')') AS field1,
CONCAT('(',STRING_AGG(CAST(field2 AS STRING) ORDER BY id), ')') AS field2
FROM (
SELECT
id, field1, field2,
COUNTIF(field2 = 100) OVER (ORDER BY id) AS grp
FROM tempTable
)
GROUP BY grp
ORDER BY MIN(id)
输出是一样不能保证 - 但是 - 不能保证在里面!!!
field1 field2
------ ------
(val1,val2) (100,110)
(val3,val4,val3,val3) (100,110,130,140)
(val1) (100)
定义连续的 – GurV
(1)我删除了MySQL标签。这似乎比BQ更偶然。 (2)SQL表格表示*无序*集合。您需要一个指定排序的列。 –