HANA SQL中按订单对相同元素的不同分组

I have a table like this:

我有这样一张桌子：

     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

The code I used for creating the "Rank" column was:

我用于创建“排名”列的代码是：

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"

I want to create a table which the ranks are sorted by "batch" and also include a "Group" column to separate each session (one session contains all the elements with the same "Key") like the one below.

我想创建一个表，其中排名按“批处理”排序，并且还包括“组”列以分隔每个会话（一个会话包含具有相同“密钥”的所有元素），如下所示。

Can you help me with it? Thank you!

你能帮帮我吗？谢谢！

     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

1 个解决方案

#1

Here is a SQLScript code which can help you to get the grouping and ranking values as you need

这是一个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

With the sample data and above SAP HANA SQLScript code execution, the following output is accessed

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

For the data to catch changing group numbers, I used SQL Lag() function which is very similar on both database platforms SAP HANA and SQL Server

为了捕获更改组号的数据，我使用了SQL Lag（）函数，它在数据库平台SAP HANA和SQL Server上非常相似

Row_Number() function with Partition By clause is an other SQLScript function (same as SQL Server) which helps me to resolve the issue.

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

I hope this is what you want

我希望这就是你想要的

#1