ORACLE-SQL-2

一、行转列

需求：根据uuid获取到一笔订单的全部打点位置和时间戳，按照打点位置来展示一笔订单的时间戳

原表：

转换后：

ID:自增长列
UUID：订单唯一id
PDACE：打点位置
TIMESTAMP：时间戳

SQL 建表源码

MAX(CASE WHEN Place=‘API_CODE’ THEN Timestamp ELSE ‘1’ END) API_CODE,
让【PDACE】列的某个值【 ‘API_CODE’】转成新列，【Timestamp】列作为行行值，【‘1’】最小值
【Uuid】多条数据的唯一值

SELECT Uuid,
        MAX(CASE WHEN Place='API_CODE' THEN Timestamp ELSE '1' END) API_CODE,
        MAX(CASE WHEN Place='API_DECODE' THEN Timestamp ELSE '1' END) API_DECODE,
        MAX(CASE WHEN Place='APP_CODE' THEN Timestamp ELSE '1' END) APP_CODE,
        MAX(CASE WHEN Place='APP_DECODE' THEN Timestamp ELSE '1' END) APP_DECODE,
        MAX(CASE WHEN Place='BUS_UP_CODE' THEN Timestamp ELSE '1' END) BUS_UP_CODE,
        MAX(CASE WHEN Place='BUS_UP_DECODE' THEN Timestamp ELSE '1' END) BUS_UP_DECODE,
        MAX(CASE WHEN Place='BUS_DOWN_CODE' THEN Timestamp ELSE '1' END) BUS_DOWN_CODE,
        MAX(CASE WHEN Place='BUS_DOWN_DECODE' THEN Timestamp ELSE '1' END) BUS_DOWN_CODE
        FROM performanceStatistics p 
GROUP BY Uuid 

二、统计
根据以上转成的新表数据，统计每个时间节点耗时的 平均值、最大值、最小值。

一共3层SELECT自内而外
1、行转换后的新表
2、每个节点计算后设置新列名a b c d
3、计算新列的平均值、最大值、最小值

SELECT AVG(su.a) AS BUS_UP_avg, MAX(su.a) AS BUS_UP_max, MIN(su.a) AS BUS_UP_min,
              AVG(su.b) AS BUS_DOWN_avg, MAX(su.b) AS BUS_DOWN_max, MIN(su.b) AS BUS_DOWN_min,
              AVG(su.c) AS API_BUS_avg, MAX(su.c) AS API_BUS_max, MIN(su.c) AS API_BUS_min,
              AVG(su.d) AS BUS_APP_avg, MAX(su.d) AS BUS_APP_max, MIN(su.d) AS BUS_APP_min
FROM (
    	SELECT (ps.BUS_UP_CODE - ps.BUS_UP_DECODE) / 1000 AS a ,
		                (ps.BUS_DOWN_CODE - ps.BUS_DOWN_DECODE) / 1000 AS b,
                     (ps.BUS_UP_DECODE - ps.API_CODE) / 1000 AS c,
                      (ps.APP_DECODE - ps.BUS_UP_CODE) / 1000 AS d
                    	FROM (
                          SELECT Uuid,
                                  MAX(CASE WHEN Place='API_CODE' THEN Timestamp ELSE '1' END) API_CODE,
                                  MAX(CASE WHEN Place='API_DECODE' THEN Timestamp ELSE '1' END) API_DECODE,
                                  MAX(CASE WHEN Place='APP_CODE' THEN Timestamp ELSE '1' END) APP_CODE,
                                  MAX(CASE WHEN Place='APP_DECODE' THEN Timestamp ELSE '1' END) APP_DECODE,
                                  MAX(CASE WHEN Place='BUS_UP_CODE' THEN Timestamp ELSE '1' END) BUS_UP_CODE,
                                  MAX(CASE WHEN Place='BUS_UP_DECODE' THEN Timestamp ELSE '1' END) BUS_UP_DECODE,
                                  MAX(CASE WHEN Place='BUS_DOWN_CODE' THEN Timestamp ELSE '1' END) BUS_DOWN_CODE,
                                  MAX(CASE WHEN Place='BUS_DOWN_DECODE' THEN Timestamp ELSE '1' END) BUS_DOWN_DECODE
                                  FROM performanceStatistics
                          GROUP BY Uuid 
	) ps
) su