需要OracleSQL中的日期增量數

需要Oracle SQL中日期遞增的次數

create table zzenp_so_multi_rs
(
so_number varchar2(30),
rev_number number,
line_number number, 
rs_date date,
status varchar2(30)
)
;

insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 1, 1, '15-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 2, 1, '16-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 3, 1, '16-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 4, 1, '14-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 5, 1, '14-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10000', 6, 1, '17-MAY-2022', 'Open');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 1, 1, '17-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 2, 1, '15-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 3, 1, '12-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 4, 1, '16-MAY-2022', 'Reference');
insert into zzenp_so_multi_rs (so_number,rev_number,line_number, rs_date, status ) values ('10001', 5, 1, '13-MAY-2022', 'Open');

在這里，我們有銷售訂單號、修訂號、行號、RS 日期和狀態。每次修改一行時，都會插入另一條記錄，并將狀態修改為 Reference for old record & Open for new record。

我需要 no_of_push_outs 的計算函式/子查詢。這僅適用于日期被推出（日期遞增）而不是被拉入的時間。

SELECT SO_NUMBER, REV_NUMBER, LINE_NUMBER, RS_DATE, <> no_of_push_outs
FROM zzenp_so_multi_rs
WHERE status = 'Open'

uj5u.com熱心網友回復：

我是這樣理解的。

tempCTE 計算 previous rs_date，然后將其用于匯總推出（因為您不能在sum聚合中具有分析功能）。

SQL> WITH
  2     temp
  3     AS
  4        (SELECT so_number,
  5                rev_number,
  6                line_number,
  7                rs_date,
  8                status,
  9                LAG (rs_date) OVER (PARTITION BY so_number ORDER BY rev_number) prev_rs_date
 10           FROM zzenp_so_multi_rs)
 11    SELECT z.so_number,
 12           z.line_number,
 13           (SELECT MAX (b.rev_number)
 14              FROM zzenp_so_multi_rs b
 15             WHERE     b.so_number = z.so_number
 16                   AND b.status = 'Open') rev_number,
 17           (SELECT MAX (b.rs_date)
 18              FROM zzenp_so_multi_rs b
 19             WHERE     b.so_number = z.so_number
 20                   AND b.status = 'Open') rs_date,
 21           SUM (CASE WHEN z.rs_date > z.prev_rs_date THEN 1 ELSE 0 END) l_sum
 22      FROM temp z
 23  GROUP BY z.so_number, z.line_number
 24  ORDER BY z.so_number;

SO_NUMBER                      LINE_NUMBER REV_NUMBER RS_DATE         L_SUM
------------------------------ ----------- ---------- ---------- ----------
10000                                    1          6 17.05.2022          2
10001                                    1          5 13.05.2022          1

SQL>

uj5u.com熱心網友回復：

對于 Oracle 版本 12.1 及更高版本，您可以使用match_recognize干凈、優雅的解決方案。

select so_number, rev_number, line_number, rs_date, no_of_push_outs
from   zzenp_so_multi_rs
match_recognize (
  partition by so_number, line_number
  order     by rev_number
  measures  last(rev_number) as rev_number,
            count(up.*)      as no_of_push_outs,
            last(rs_date)    as rs_date
  pattern   ( (up|other)* )
  define    up as rs_date > prev(rs_date)
);

uj5u.com熱心網友回復：

也LAG()用于獲取日期數量增加，但希望更簡單的加入

with pout as (
select so_number, count(*) no_of_push_outs  from (
select z.so_number, rs_date, 
lag(rs_date) over (partition by so_number order by rev_number) p_rs_date
from zzenp_so_multi_rs z
)
where rs_date > p_rs_date
group by so_number
)
select z.*, pout.no_of_push_outs
from zzenp_so_multi_rs z, pout
where z.so_number=pout.so_number
and z.status='Open';

uj5u.com熱心網友回復：

您可以使用LAG和COUNT分析函式（沒有任何自聯接）：

SELECT *
FROM   (
  SELECT so_number,
         rev_number,
         line_number,
         rs_date,
         status,
         COUNT(is_push_out) OVER (PARTITION BY so_number) AS no_of_push_outs
  FROM    (
    SELECT z.*,
           CASE
           WHEN LAG(rs_date) OVER (PARTITION BY so_number ORDER BY rev_number)
                  < rs_date
           THEN 1
           END AS is_push_out
    FROM   zzenp_so_multi_rs z
  )
)
WHERE  status = 'Open';

或者，您可以使用LAG分析函式，然后使用GROUP BYandKEEP (DENSE_RANK LAST ORDER BY rev_number)獲取聚合函式中最新修訂的資料：

SELECT so_number,
       MAX(rev_number) AS rev_number,
       MAX(line_number) KEEP (DENSE_RANK LAST ORDER BY rev_number)
         AS line_number,
       MAX(rs_date) KEEP (DENSE_RANK LAST ORDER BY rev_number)
         AS rs_date,
       MAX(status) KEEP (DENSE_RANK LAST ORDER BY rev_number)
         AS status,
       COUNT(is_push_out) AS no_of_push_outs
FROM    (
  SELECT z.*,
         CASE
         WHEN LAG(rs_date) OVER (PARTITION BY so_number ORDER BY rev_number)
                < rs_date
         THEN 1
         END AS is_push_out
  FROM   zzenp_so_multi_rs z
)
GROUP BY so_number;

其中，對于樣本資料，兩者都輸出：

SO_NUMBER	REV_NUMBER	電話號碼	RS_DATE	地位	NO_OF_PUSH_OUTS
10000	6	1	22 年 5 月 17 日	打開	2
10001	5	1	22 年 5 月 13 日	打開	1

db<>在這里擺弄

標籤：sql 甲骨文

上一篇：OracleROWNUM分頁編號問題

下一篇：In-Memory是一種額外成本的選擇嗎？（甲骨文）