oracle 如何将数据与大约一个月前的数据进行比较？

pokxtpni 于 2023-03-07 发布在 Oracle

关注(0)|答案(2)|浏览(130)

我有一个表，其中包含不同站点的耗电量数据。这些数据在时间上是不规则的。这意味着，有时我们假设数据是某一天的一个站点的，而同一站点的下一个记录是另一天的。不一定是一个月之后。它是列DATE_DE_DEBUT_DE_PERIODE_DE_CONSOMMATION。我想与同一地点一个月前的记录进行比较。比较意味着查找CONSOMMATION_TOTALE__KWH_列中的值之间的差异百分比。
例如，如果我有以下记录：
| 确认日期|确认_总计__千瓦时_|
| - ------|- ------|
| 2018年1月2日|一百|
| 2018年1月16日|八十|
| 2018年2月1日|五十|
在此示例中，结果将为：
| 研究中心|百分比|
| - ------|- ------|
| 阿赫雷斯P7瓦伦顿|百分之五十|
我们在变量中选择年份：YEAR，例如'2018'，以及带有月份编号的月份，在本例中为'02'。

CREATE TABLE "FL_ENGIE_ELEC" 
   (    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "DÉSIGNATION_SITE" VARCHAR2(255 BYTE), 
    "DATE_DE_DÉBUT_DE_PÉRIODE_DE_CONSOMMATION" DATE, 
    "DATE_DE_FIN_DE_PÉRIODE_DE_CONSOMMATION" DATE, 
    "CONSOMMATION_TOTALE__KWH_" NUMBER, 
     PRIMARY KEY ("ID"))

一些示例数据：

(ID,"DÉSIGNATION_SITE","DATE_DE_DÉBUT_DE_PÉRIODE_DE_CONSOMMATION","DATE_DE_FIN_DE_PÉRIODE_DE_CONSOMMATION",CONSOMMATION_TOTALE__KWH_) values (1307,'BNE99782 STEU DU MOULIN DE GRANVILLE',to_date('25/01/2022 00:00','DD/MM/YYYY HH24:MI'),to_date('02/03/2022 00:00','DD/MM/YYYY HH24:MI'),26298);
Insert into FL_ENGIE_ELEC (ID,"DÉSIGNATION_SITE","DATE_DE_DÉBUT_DE_PÉRIODE_DE_CONSOMMATION","DATE_DE_FIN_DE_PÉRIODE_DE_CONSOMMATION",CONSOMMATION_TOTALE__KWH_) values (1308,'BNE99782 STEU DU MOULIN DE GRANVILLE',to_date('25/12/2021 00:00','DD/MM/YYYY HH24:MI'),to_date('24/01/2022 00:00','DD/MM/YYYY HH24:MI'),16855);
Insert into FL_ENGIE_ELEC (ID,"DÉSIGNATION_SITE","DATE_DE_DÉBUT_DE_PÉRIODE_DE_CONSOMMATION","DATE_DE_FIN_DE_PÉRIODE_DE_CONSOMMATION",CONSOMMATION_TOTALE__KWH_) values (1309,'BNE99782 STEU DU MOULIN DE GRANVILLE',to_date('03/03/2022 00:00','DD/MM/YYYY HH24:MI'),to_date('04/04/2022 00:00','DD/MM/YYYY HH24:MI'),30352);
Insert into FL_ENGIE_ELEC (ID,"DÉSIGNATION_SITE","DATE_DE_DÉBUT_DE_PÉRIODE_DE_CONSOMMATION","DATE_DE_FIN_DE_PÉRIODE_DE_CONSOMMATION",CONSOMMATION_TOTALE__KWH_) values (1310,'BNE99782 STEU DU MOULIN DE GRANVILLE',to_date('05/04/2022 00:00','DD/MM/YYYY HH24:MI'),to_date('03/05/2022 00:00','DD/MM/YYYY HH24:MI'), 2723);
Insert into FL_ENGIE_ELEC (ID,"DÉSIGNATION_SITE","DATE_DE_DÉBUT_DE_PÉRIODE_DE_CONSOMMATION","DATE_DE_FIN_DE_PÉRIODE_DE_CONSOMMATION",CONSOMMATION_TOTALE__KWH_) values (1311,'BNE99782 STEU DU MOULIN DE GRANVILLE',to_date('04/05/2022 00:00','DD/MM/YYYY HH24:MI'),to_date('24/05/2022 00:00','DD/MM/YYYY HH24:MI'),9878);
Insert into FL_ENGIE_ELEC (ID,"DÉSIGNATION_SITE","DATE_DE_DÉBUT_DE_PÉRIODE_DE_CONSOMMATION","DATE_DE_FIN_DE_PÉRIODE_DE_CONSOMMATION",CONSOMMATION_TOTALE__KWH_) values (1312,'BNE99782 STEU DU MOULIN DE GRANVILLE',to_date('25/05/2022 00:00','DD/MM/YYYY HH24:MI'),to_date('23/06/2022 00:00','DD/MM/YYYY HH24:MI'),13228);
Insert into FL_ENGIE_ELEC (ID,"DÉSIGNATION_SITE","DATE_DE_DÉBUT_DE_PÉRIODE_DE_CONSOMMATION","DATE_DE_FIN_DE_PÉRIODE_DE_CONSOMMATION",CONSOMMATION_TOTALE__KWH_) values (1313,'BNE99782 STEU DU MOULIN DE GRANVILLE',to_date('24/06/2022 00:00','DD/MM/YYYY HH24:MI'),to_date('24/07/2022 00:00','DD/MM/YYYY HH24:MI'),13385);
Insert into FL_ENGIE_ELEC (ID,"DÉSIGNATION_SITE","DATE_DE_DÉBUT_DE_PÉRIODE_DE_CONSOMMATION","DATE_DE_FIN_DE_PÉRIODE_DE_CONSOMMATION",CONSOMMATION_TOTALE__KWH_) values (1314,'BNE99782 STEU DU MOULIN DE GRANVILLE',to_date('25/07/2022 00:00','DD/MM/YYYY HH24:MI'),to_date('24/08/2022 00:00','DD/MM/YYYY HH24:MI'),22449);

我无法编写适当的"select"语句。

with t as (
  2  select 1 site, date '2023-01-01' d, 20 kwh from dual union all
  3  select 1 site, date '2023-01-31' d, 21 kwh from dual union all
  4  select 1 site, date '2023-02-01' d, 23 kwh from dual union all
  5  select 1 site, date '2023-02-02' d, 24 kwh from dual union all
  6  select 1 site, date '2023-02-03' d, 25 kwh from dual union all
  7  select 1 site, date '2023-02-04' d, 26 kwh from dual union all
  8  select 1 site, date '2023-03-03' d, 27 kwh from dual union all
  9  select 2 site, date '2023-01-01' d, 12 kwh from dual union all
 10  select 2 site, date '2023-01-17' d, 15 kwh from dual union all
 11  select 2 site, date '2023-04-01' d, 22 kwh from dual union all
 12  select 2 site, date '2023-04-11' d, 17 kwh from dual)
 13  select  t.*
 14  ,last_value(kwh)
 15      over (partition by site
 16      order by d
 17      range between UNBOUNDED PRECEDING and interval '1' month preceding) lv2
 18  from t
 19* order by 1.2

在这个例子中，我没有得到百分比。
最好的问候。

oracle

来源：https://stackoverflow.com/questions/75628054/how-to-compare-data-with-data-approximately-one-month-before

2条答案

按热度按时间

juzqafwq1#

with t as (
select 1 site, date '2023-01-01' d, 20 kwh 
from dual union all
select 1 site, date '2023-01-31' d, 21 kwh 
from dual union all  
select 1 site, date '2023-02-01' d, 23 kwh 
from dual union all  
select 1 site, date '2023-02-02' d, 24 kwh 
from dual union all  
select 1 site, date '2023-02-03' d, 25 kwh 
from dual union all 
select 1 site, date '2023-02-04' d, 26 kwh 
from dual union all 
select 1 site, date '2023-03-03' d, 27 kwh 
from dual union all 
select 2 site, date '2023-01-01' d, 12 kwh 
from dual union all 
select 2 site, date '2023-01-17' d, 15 kwh 
from dual union all 
select 2 site, date '2023-04-01' d, 22 kwh 
from dual union all  
select 2 site, date '2023-04-11' d, 17 kwh 
from dual) 
,t2 as (
select t.* 
,last_value(kwh) over (partition by site order by d range between UNBOUNDED PRECEDING and interval '1' month preceding) prev_kwh 
from t 
)
select t2.*, round(kwh/prev_kwh*100, 2) pct
from t2
order by 1, 2

为了得到百分比，只需要加上一点东西。

赞(0）回复(0）举报 2023-03-07

w51jfk4q2#

使用此示例数据：

WITH 
    tbl (SITE, S_DATE, KWH) as 
        (
            Select 1, To_Date('2023-01-01', 'yyyy-mm-dd'), 20 From Dual Union All
            Select 1, To_Date('2023-01-31', 'yyyy-mm-dd'), 21 From Dual Union All
            Select 1, To_Date('2023-02-01', 'yyyy-mm-dd'), 23 From Dual Union All
            Select 1, To_Date('2023-02-02', 'yyyy-mm-dd'), 24 From Dual Union All
            Select 1, To_Date('2023-02-03', 'yyyy-mm-dd'), 25 From Dual Union All
            Select 1, To_Date('2023-02-04', 'yyyy-mm-dd'), 26 From Dual Union All
            Select 1, To_Date('2023-03-03', 'yyyy-mm-dd'), 27 From Dual Union All
            Select 2, To_Date('2023-01-01', 'yyyy-mm-dd'), 12 From Dual Union All
            Select 2, To_Date('2023-01-17', 'yyyy-mm-dd'), 15 From Dual Union All
            Select 2, To_Date('2023-04-01', 'yyyy-mm-dd'), 16 From Dual Union All
            Select 2, To_Date('2023-04-11', 'yyyy-mm-dd'), 17 From Dual
        ),

...您可以创建一个cte（网格），将两行中的值收集到一行中，这样您就可以将KWH与PREV_KWH和KWH_DIFF放在同一行中...此处使用了具有降序日期的LEAD（）分析函数...

grid as
        (
            Select  t.*, 
                    Nvl(LEAD(t.KWH) OVER(Partition By t.SITE Order By t.S_DATE DESC), KWH) "KWH_PREV", 
                    t.KWH - Nvl(LEAD(t.KWH) OVER(Partition By SITE Order By S_DATE DESC), 0) "KWH_DIFF",
                    t.S_DATE - Nvl(LEAD(t.S_DATE) OVER(Partition By SITE Order By S_DATE DESC), t.S_DATE) "DAY_DIFF",
                    Min(t.S_DATE) OVER(Partition By SITE, To_Char(t.S_DATE, 'yyyymm')) "MIN_MONTH_DATE"
            From tbl t
            Order By t.SITE, t.S_DATE DESC
        )
--
--  g r i d    R e s u l t:
      SITE S_DATE           KWH   KWH_PREV   KWH_DIFF   DAY_DIFF MIN_MONTH_DATE
---------- --------- ---------- ---------- ---------- ---------- --------------
         1 03-MAR-23         27         26          1         27 03-MAR-23      
         1 04-FEB-23         26         25          1          1 01-FEB-23      
         1 03-FEB-23         25         24          1          1 01-FEB-23      
         1 02-FEB-23         24         23          1          1 01-FEB-23      
         1 01-FEB-23         23         21          2          1 01-FEB-23      
         1 31-JAN-23         21         20          1         30 01-JAN-23      
         1 01-JAN-23         20         20         20          0 01-JAN-23      
         2 11-APR-23         17         16          1         10 01-APR-23      
         2 01-APR-23         16         15          1         74 01-APR-23      
         2 17-JAN-23         15         12          3         16 01-JAN-23      
         2 01-JAN-23         12         12         12          0 01-JAN-23

...使用网格的结果数据集，您可以计算任何您想要的。以下是每月KWH_DIFF相对于当前和以前KWH的百分比计算，比较每个月的第一个KWH阅读。如果这不适合您，您可以用另一种方式进行计算

--   M a i n    S Q L        
Select  SITE, S_DATE, To_Char(S_DATE, 'Mon, yyyy') "MONTH", KWH, 
        Nvl(LEAD(KWH) OVER(Partition By SITE Order By S_DATE DESC), KWH) "KWH_PREV", 
        KWH - Nvl(LEAD(KWH) OVER(Partition By SITE Order By S_DATE DESC), KWH) "KWH_DIFF",
        Round((KWH - Nvl(LEAD(KWH) OVER(Partition By SITE Order By S_DATE DESC), KWH)) * 100 / KWH, 2) "DIFF_PERCENT_1",
        Round((KWH - Nvl(LEAD(KWH) OVER(Partition By SITE Order By S_DATE DESC), KWH)) * 100 / (Nvl(LEAD(KWH) OVER(Partition By SITE Order By S_DATE DESC), KWH)), 2) "DIFF_PERCENT_2"
From    grid g
Where   s_DATE = MIN_MONTH_DATE
--  
--  R e s u l t :
      SITE S_DATE    MONTH            KWH   KWH_PREV   KWH_DIFF DIFF_PERCENT_1 DIFF_PERCENT_2
---------- --------- --------- ---------- ---------- ---------- -------------- --------------
         1 03-MAR-23 Mar, 2023         27         23          4          14.81          17.39 
         1 01-FEB-23 Feb, 2023         23         20          3          13.04             15 
         1 01-JAN-23 Jan, 2023         20         20          0              0              0 
         2 01-APR-23 Apr, 2023         16         12          4             25          33.33 
         2 01-JAN-23 Jan, 2023         12         12          0              0              0

...如果您想要某个特定月份的结果，只需将条件添加到现有WHERE子句中...

赞(0）回复(0）举报 2023-03-07

我来回答

oracle 如何将数据与大约一个月前的数据进行比较？

2条答案

相关问题

热门标签

最新问答