时间戳

首页 > 解决方案 > 使用各自的记录将 ROW 转换为列

sql - 使用各自的记录将 ROW 转换为列

问题描述

使用各自的记录将 ROW 转换为列

我需要 4 列

TYPE   |    COUNT    |    PERIOD     |   VARIANCE
TRADE      50               JAN            0%
TRADE      100              FEB            100%

我正在使用此查询并仅获取三列

with b1 as
(
SELECT 

      COUNT(DISTINCT T.SALE)AS TRADE,
      TO_CHAR(T.DATE,'YYYY-MON') As Period
        FROm  TRADE T 
          JOIN ORDER O ON T.Account = O.Account
        WHERE
           T.DATE between date '2020-01-01' and date '2020-01-31'  --Last 
                group by TO_CHAR(T.DATE,'YYYY-MON')

UNION ALL

SELECT 

      COUNT(DISTINCT T.SALE) AS TRADE,
      TO_CHAR(T.DATE,'YYYY-MON') As Period
        FROm  TRADE T 
          JOIN ORDER O ON T.Account = O.Account
        WHERE
           T.DATE between date '2020-02-01' and date '2020-02-28'  --Last 
         group by TO_CHAR(T.DATE,'YYYY-MON'))

select  TRADE, Period,
       100*(TRADE-lag(TRADE,1,TRADE) over (order by period))
       /lag(TRADE,1,TRADE) over (order by period)
       as "variance(%)"
  from b1
 order by period

标签： sqloraclepivotcommon-table-expression

解决方案

看起来你真的很亲近。这是你要找的吗？

with b1 as
(
SELECT 
 "TRADE" as Type,
      COUNT(DISTINCT T.SALE)AS Count,
      TO_CHAR(T.DATE,'YYYY-MON') As Period
        FROm  TRADE T 
          JOIN ORDER O ON T.Account = O.Account
        WHERE
           T.DATE between date '2020-01-01' and date '2020-01-31'  --Last 
                group by TO_CHAR(T.DATE,'YYYY-MON')

UNION ALL

SELECT 
"TRADE" as TYPE,
      COUNT(DISTINCT T.SALE) AS COUNT,
      TO_CHAR(T.DATE,'YYYY-MON') As Period
        FROm  TRADE T 
          JOIN ORDER O ON T.Account = O.Account
        WHERE
           T.DATE between date '2020-02-01' and date '2020-02-28'  --Last 
         group by TO_CHAR(T.DATE,'YYYY-MON'))

select  TYPE, COUNT, Period,
       100*(TRADE-lag(TRADE,1,TRADE) over (order by period))
       /lag(TRADE,1,TRADE) over (order by period)
       as "variance(%)"
  from b1
 order by period

推荐阅读