sql - 创建一个Postgres交叉表查询并计算列之间的差异

Tni分析
我有格式的数据

Patid | TNT     | date
 A123 | 1.2.    | 23/1/2012
 A123 | 1.3.    | 23/1/2012
 B123 | 2.6.    | 24/7/2011
 B123 | 2.7.    | 24/7/2011

我希望能计算出两行之间的差

rowid. | TNT-1. | TNT-2. | difference
A123.  |. 1.2.  | 1.3.   | 0.1
B123.  | 2.6.   | 2.7.   | 0.1

Etc

我想这是Postgres中cross-tab函数的一个用法，但是很难得到结果。非常感谢您的帮助。

最佳答案

您可以手动旋转，然后考虑差异（假设每个Patid始终有两个记录，并且不必考虑date）：

with cte1 as (
    select
        Patid, TNT, date, row_number() over(partition by Patid order by TNT) as rn
    from Table1
), cte2 as (
    select
        Patid,
        max(case when rn = 1 then TNT end) as "TNT-1",
        max(case when rn = 2 then TNT end) as "TNT-2"
    from cte1
    group by Patid
)
select
    Patid as rowid, "TNT-1", "TNT-2", "TNT-2" - "TNT-1" as difference
from cte2

-------------------------------------
ROWID    TNT-1    TNT-2    DIFFERENCE
A123       1.2      1.3           0.1
B123       2.6      2.7           0.1

sql fiddle demo