分组连续记录

Question

这是我的记录（输入）。所述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) 

如何做到这一点

Answer 1

让我假设你有一个指定排序的列。然后，你可以通过每个记录之前计数的“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开头。

Answer 2

您必须有一些可用于定义订单的字段。
在下面的例子，我认为它是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)