在HANA SQL中按订单对相同要素进行不同分组

Question

我有一张这样的桌子：

     NanoTime            Sensor   Key   Rank
15,899,129,832,916     Gyroscope   i     1
15,899,132,632,874     Gyroscope   i     2
15,899,152,377,999     Gyroscope   i     3
15,900,080,214,835     Gyroscope   o     1
15,900,092,388,626     Gyroscope   o     2
15,900,112,529,501     Gyroscope   o     3
15,971,592,577,285     Gyroscope   i     4
15,971,592,739,660     Gyroscope   i     5
15,971,612,339,952     Gyroscope   i     6
15,971,632,305,202     Gyroscope   i     7
15,972,579,736,201     Gyroscope   o     4
15,972,592,583,743     Gyroscope   o     5
15,972,612,371,701     Gyroscope   o     6

我用来创建"Rank“列的代码是：

SELECT "NanoTime","Sensor", "Key",
    ROW_NUMBER() OVER ( PARTITION BY "Sensor", "Key" ORDER BY "NanoTime" ASC) as RANK
FROM TEST
WHERE "Sensor" = 'Gyroscope'
GROUP BY "NanoTime","Sensor", "Key"

我想创建一个表，其中的排名是按“批处理”排序的，还包括一个“组”列来分隔每个会话(一个会话包含具有相同“键”的所有元素)，如下图所示。

你能帮我弄一下吗？谢谢!

     NanoTime            Sensor   Key   Rank    Group
15,899,129,832,916     Gyroscope   i     1       1
15,899,132,632,874     Gyroscope   i     2       1
15,899,152,377,999     Gyroscope   i     3       1
15,900,080,214,835     Gyroscope   o     1       2
15,900,092,388,626     Gyroscope   o     2       2
15,900,112,529,501     Gyroscope   o     3       2
15,971,592,577,285     Gyroscope   i     1       3
15,971,592,739,660     Gyroscope   i     2       3
15,971,612,339,952     Gyroscope   i     3       3
15,971,632,305,202     Gyroscope   i     4       3
15,972,579,736,201     Gyroscope   o     1       4
15,972,592,583,743     Gyroscope   o     2       4
15,972,612,371,701     Gyroscope   o     3       4

Answer 1

下面是一段SQLScript代码，它可以帮助您根据需要获取分组和排名值

--create table Nano ( NanoTime varchar(30), Sensor varchar(30), key char(1))
/*
insert into Nano values ('15,899,129,832,916','Gyroscope','i');--     1
insert into Nano values ('15,899,132,632,874','Gyroscope','i');--     2
insert into Nano values ('15,899,152,377,999','Gyroscope','i');--     3
insert into Nano values ('15,900,080,214,835','Gyroscope','o');--     1
insert into Nano values ('15,900,092,388,626','Gyroscope','o');--     2
insert into Nano values ('15,900,112,529,501','Gyroscope','o');--     3
insert into Nano values ('15,971,592,577,285','Gyroscope','i');--     4
insert into Nano values ('15,971,592,739,660','Gyroscope','i');--     5
insert into Nano values ('15,971,612,339,952','Gyroscope','i');--     6
insert into Nano values ('15,971,632,305,202','Gyroscope','i');--     7
insert into Nano values ('15,972,579,736,201','Gyroscope','o');--     4
insert into Nano values ('15,972,592,583,743','Gyroscope','o');--     5
insert into Nano values ('15,972,612,371,701','Gyroscope','o');--     6
*/

with cte as (
    select
        *, 
        lag(Sensor,1,'') over (order by NanoTime) previousSensor,
        lag(key,1,'') over (order by NanoTime) previouskey,
        case when 
            lag(Sensor,1,'') over (order by NanoTime) = Sensor and
            lag(key,1,'') over (order by NanoTime) = key
            then 0 else 1 end ischange
    from Nano
    order by NanoTime
)
select NanoTime, Sensor, Key, Row_Number() Over (Partition By GroupNo Order By NanoTime) as Rank, GroupNo
from (
select cte.*, (select sum(x.ischange) from cte x where x.NanoTime <= cte.NanoTime) as groupno
from cte
) t

使用示例数据和上面的SAP HANA SQLScript代码执行，可以访问以下输出

​

​

为了让数据捕捉到组编号的变化，我使用了SQL Lag() function，它在SAP HANA和SQL Server这两个数据库平台上非常相似

带有Partition By子句的Row_Number()函数是另一个SQLScript函数(与SQL Server相同)，它可以帮助我解决这个问题。

我希望这就是你想要的