获取和指定日期前一天的对比数据，这样的sql语句该怎么写呢

Question

设表结构如下：rq        sl   pid
2012-9-1  50    1
2012-9-1  30    2
2012-8-31 45    1
2012-8-31 36    2获取9月1日的数据，按照pid相同，得出sl和前一天的差，结果类似如下
rq        sl   bh  pid 
2012-9-1  50   5    1
2012-9-1  30   -6   2

Answer 1

with t (rq, sl, pid) as (
select to_date('2012-9-1','yyyy-mm-dd'), 50, 1 from dual union all
select to_date('2012-9-1','yyyy-mm-dd'), 30, 2  from dual union all
select to_date('2012-8-31','yyyy-mm-dd'), 45, 1  from dual union all
select to_date('2012-8-31','yyyy-mm-dd'), 36, 2  from dual)
select * from (
select rq, sl,
sl-(select sl from t t1 where t1.rq=t.rq-1 and t1.pid=t.pid) bh, pid from t)
where bh is not null;

Answer 2

托大了,我以为这个SQL可以直接查询出来!汗....WITH FOO AS(
 SELECT '2012-9-1' AS RQ,50 AS SL,1 AS PID FROM DUAL
 UNION
 --这个地方把LZ的RQ修改成9-2,否则sql会出错
 SELECT '2012-9-2' AS RQ,30 AS SL,2 AS PID FROM DUAL
 UNION
 SELECT '2012-8-31' AS RQ,45 AS SL,1 AS PID FROM DUAL
 UNION
 SELECT '2012-8-31' AS RQ,36 AS SL,2 AS PID FROM DUAL
)
SELECT MPID,SUM(ASL)-SUM(BSL) AS BH,PID
FROM (
SELECT MAX(RQ) OVER(PARTITION BY PID ORDER BY PID) AS MPID, 
   CASE WHEN MAX(RQ) OVER(PARTITION BY PID ORDER BY PID) = RQ THEN SL ELSE 0 END AS ASL,
   CASE WHEN MAX(RQ) OVER(PARTITION BY PID ORDER BY PID)!= RQ THEN SL ELSE 0 END AS BSL,
    PID
  FROM FOO
) O GROUP BY PID,MPID
查询结果:MPID    BH     PID
2012-9-1 5 1
2012-9-2 -6 2

Answer 3

--这个地方把LZ的RQ修改成9-2,否则sql会出错
为啥会报错？

Answer 4

--试试LAG函数
--rq sl pid
with t as (
select date'2012-9-1' as fdate, 50 as QUANTITY, 1 as fid from dual
union all
select date'2012-9-1', 30, 2 from dual
union all
select date'2012-8-31', 45, 1 from dual
union all
select date'2012-8-31', 36, 2 from dual
)
select fdate,quantity,fid,lag(QUANTITY)over(partition by fid order by fdate) as lastDay from tFDATE                     QUANTITY               FID                    LASTDAY                
------------------------- ---------------------- ---------------------- ---------------------- 
2012-08-31 00:00:00       45                     1                                             
2012-09-01 00:00:00       50                     1                      45                     
2012-08-31 00:00:00       36                     2                                             
2012-09-01 00:00:00       30                     2                      36                     

Answer 5

WITH t AS
  ( SELECT DATE'2012-9-1' AS fdate, 50 AS QUANTITY, 1 AS fid FROM dual
  UNION ALL
  SELECT DATE'2012-9-1', 30, 2 FROM dual
  UNION ALL
  SELECT DATE'2012-8-31', 45, 1 FROM dual
  UNION ALL
  SELECT DATE'2012-8-31', 36, 2 FROM dual
  )
SELECT fdate,
  quantity,
  fid,
  lastday,
  DECODE(lastday,NULL,quantity,quantity-lastday) fresult
FROM
  (SELECT fdate,
    quantity,
    fid,
    lag(QUANTITY)over(partition BY fid order by fdate) AS lastDay
  FROM t
  )FDATE                     QUANTITY               FID                    LASTDAY                FRESULT                
------------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
2012-08-31 00:00:00       45                     1                                             45                     
2012-09-01 00:00:00       50                     1                      45                     5                      
2012-08-31 00:00:00       36                     2                                             36                     
2012-09-01 00:00:00       30                     2                      36                     -6                     

Answer 6

学习了,感谢分享...
LAG分析函数,是将分区之后的排序结果第1位取了出来,解决了这个问题里面的聚合之后没法得到36的那一列值的问题,修改之后:WITH FOO AS(
 SELECT DATE'2012-9-1' AS RQ,50 AS SL,1 AS PID FROM DUAL
 UNION
 SELECT DATE'2012-9-2' ,30 ,2  FROM DUAL
 UNION
 SELECT DATE'2012-8-31',45 ,1  FROM DUAL
 UNION
 SELECT DATE'2012-8-31',36 ,2  FROM DUAL
)SELECT  MAX(O.RQ) AS RQ,SUM(O.BH),O.PID FROM
(SELECT RQ,SL-LAG(SL)OVER(PARTITION BY PID ORDER BY RQ) AS BH,PID FROM FOO) O
GROUP BY O.PID

Answer 7

WITH FOO AS(
 SELECT DATE'2012-9-1' AS RQ,50 AS SL,1 AS PID FROM DUAL
 UNION
 SELECT DATE'2012-9-2' ,30 ,2  FROM DUAL
 UNION
 SELECT DATE'2012-8-31',45 ,1  FROM DUAL
 UNION
 SELECT DATE'2012-8-31',36 ,2  FROM DUAL
)SELECT  MAX(O.RQ) AS RQ,SUM(O.BH),O.PID FROM
(SELECT RQ,SL-LEAD(SL)OVER(PARTITION BY PID ORDER BY RQ DESC) AS BH,PID FROM FOO) O
GROUP BY O.PID

Answer 8

我最后还是用left join 实现了