1
votes

ORACLE - Intervalle entre les dates ligne par ligne

Bonjour, dites-moi, s'il vous plaît, comment briser l'intervalle entre les dates ligne par ligne. J'ai une table:

SELECT trunc(t.start_dte) + level -1 dte FROM (select * from data 
where data.end_dte != to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss')) t
CONNECT BY level < trunc(t.end_dte) - trunc(t.start_dte) + 1

Je veux obtenir le résultat:

with result_data as (
SELECT 1 AS id, 232 AS status, to_date('21.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 232 AS status, to_date('22.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 232 AS status, to_date('23.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 232 AS status, to_date('24.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 235 AS status, to_date('25.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 235 AS status, to_date('26.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 233 AS status, to_date('27.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 233 AS status, to_date('28.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('20.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('21.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('22.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('23.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('24.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('25.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 259 AS status, to_date('26.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 259 AS status, to_date('27.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 259 AS status, to_date('28.08.2019') AS dte FROM dual)

select * from result_data

J'ai essayé de faire via cette requête, mais rien n'a fonctionné pour moi

with data as (
SELECT 1 AS id, 232 AS status, to_date('21.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('25.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 1 AS id, 235 AS status, to_date('25.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('27.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 1 AS id, 233 AS status, to_date('27.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('20.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('23.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('23.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('26.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 259 AS status, to_date('26.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual)

select * from data

Il y a beaucoup de lignes dans le tableau, y a-t-il des réflexions sur la façon de procéder rationnellement?

sql oracle

0 commentaires

3 Réponses :

1
votes

Ok, je comprends maintenant. Le processus s'appelle la densification des données. Vous pouvez google pour cela.

with data as (
SELECT 1 AS id, 232 AS status, to_date('21.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('25.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 1 AS id, 235 AS status, to_date('25.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('27.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 1 AS id, 233 AS status, to_date('27.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('20.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('23.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('23.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('26.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 259 AS status, to_date('26.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual)
,ref_dates as ( select (select min(trunc(start_dte)) from data) + level dd from dual 
                                                                        connect by (select min(trunc(start_dte)) from data) + level 
                                                                            <= (select max(trunc(end_dte)) from data where end_dte != to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss')) )  
select id,status,dd vol from data partition by (status)
left join ref_dates x on (dd >=trunc(start_dte) and dd <= trunc(end_dte)   )
where 1=1
order by  status,dd

2 commentaires

Arkadiusz Łukasiewicz, l'utilisateur souhaite générer les lignes manquantes avec le montant maximum disponible jusqu'à cette date.

@AnkitBajpai Vous avez raison! Avec valeur de statut.

2
votes

J'ai commenté les plages de dates qui se terminent en 2999 sur votre exemple pour que le résultat soit plus clair:

with
maxdays
as
(select max( end_dte-start_dte )+1 days from 
    (SELECT 1 AS id, 232 AS status, to_date('21.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('25.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     UNION ALL
     SELECT 1 AS id, 235 AS status, to_date('25.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('27.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     --UNION ALL
     --SELECT 1 AS id, 233 AS status, to_date('27.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     UNION ALL
     SELECT 2 AS id, 301 AS status, to_date('20.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('23.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     UNION ALL
     SELECT 2 AS id, 264 AS status, to_date('23.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('26.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     --UNION ALL
     --SELECT 2 AS id, 259 AS status, to_date('26.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
    ) 
),
data
as
(select level-1 l from maxdays connect by level <= days )
select id,status,start_dte+l
  from data, (SELECT 1 AS id, 232 AS status, to_date('21.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('25.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     UNION ALL
     SELECT 1 AS id, 235 AS status, to_date('25.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('27.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     --UNION ALL
     --SELECT 1 AS id, 233 AS status, to_date('27.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     UNION ALL
     SELECT 2 AS id, 301 AS status, to_date('20.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('23.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     UNION ALL
     SELECT 2 AS id, 264 AS status, to_date('23.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('26.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
     --UNION ALL
     --SELECT 2 AS id, 259 AS status, to_date('26.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
    )
 where l <= end_dte-start_dte
 order by 2,3,1;

1 commentaires

Excellent! C'est ce dont j'ai besoin! Merci beaucoup.

0
votes

Je pense que la bonne réponse ne produit pas le résultat souhaité.

Vous pouvez essayer la requête suivante:

SQL> with data as (
  2  SELECT 1 AS id, 232 AS status, to_date('21.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('25.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual UNION ALL
  3  SELECT 1 AS id, 235 AS status, to_date('25.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('27.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual UNION ALL
  4  SELECT 1 AS id, 233 AS status, to_date('27.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual UNION ALL
  5  SELECT 2 AS id, 301 AS status, to_date('20.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('23.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual UNION ALL
  6  SELECT 2 AS id, 264 AS status, to_date('23.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('26.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual UNION ALL
  7  SELECT 2 AS id, 259 AS status, to_date('26.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual)
  8  ,D AS (select ID, STATUS, TRUNC(start_dte) AS start_dte, TRUNC(end_dte) AS end_dte from data)
  9  SELECT
 10      ID,
 11      STATUS,
 12      START_DTE
 13  FROM
 14      (
 15          SELECT DISTINCT
 16              ID,
 17              STATUS,
 18              START_DTE + LEVEL - 1 AS START_DTE,
 19              END_DTE
 20          FROM
 21              D
 22          CONNECT BY
 23              LEVEL <= TRUNC(CASE
 24                  WHEN END_DTE = DATE '2999-12-31' THEN START_DTE + 1
 25                  ELSE END_DTE
 26              END) - START_DTE + 1
 27      )
 28  WHERE
 29      START_DTE != END_DTE
 30  ORDER BY
 31      ID,
 32      START_DTE
 33  ;

        ID     STATUS START_DTE
---------- ---------- ---------
         1        232 21-AUG-19
         1        232 22-AUG-19
         1        232 23-AUG-19
         1        232 24-AUG-19
         1        235 25-AUG-19
         1        235 26-AUG-19
         1        233 27-AUG-19
         1        233 28-AUG-19
         2        301 20-AUG-19
         2        301 21-AUG-19
         2        301 22-AUG-19

        ID     STATUS START_DTE
---------- ---------- ---------
         2        264 23-AUG-19
         2        264 24-AUG-19
         2        264 25-AUG-19
         2        259 26-AUG-19
         2        259 27-AUG-19

16 rows selected.

SQL>

Cheers !!

p >

0 commentaires