Home > Software engineering >  Oracle SQL Query to properly position the data based on time intervals
Oracle SQL Query to properly position the data based on time intervals

Time:06-06

I have table load_ext which is an external table for the below file structure

customer interval_type data_count Start_time interval1 interval2 interval3 ,..interval24
67891 60 5 06022022040000AM 0.07 0.767 0.65 0.69 0 0...
12345 60 8 06022022120000PM 0.07 0.767 0.65 0.69 0.767 0.69 0 0

To explain the above columns, All columns are varchar2. Interval type is in minutes, data_count column says the number of intervals to be posted starting from the start_time column, Interval1 is the value for 00:00:00 to 01:00:00 AM and likewise. Target table will have the same structure but the above intervals should be moved to the respective columns. For example, the value of interval1 column in the first row should be moved to column interval4 and the same for all other columns to the respective interval periods.

My target table should have the data like below

customer interval_type data_count Start_time interval1 interval2 interval3 interval4 interval5 ..interval24
67891 60 5 06022022040000AM 0 0 0 0.07 0.767 0.65 0.81 0 0
12345 60 8 06022022120000PM 0 0 0 0 0 0 0 0 0.07 0.65 0.07 0.65 0 0...

I am providing the table data with ',' delimiter as the table structure is too big to post in the same format. This has to be done in Oracle only, we are using Oracle 19.

CodePudding user response:

Unpivot the columns to rows, add the hours from the start time to the interval and then pivot back to columns:

SELECT *
FROM   (
  SELECT customer,
         interval_type
         data_count,
         start_time,
         MOD(
           interval_name   TO_CHAR(TO_DATE(start_time, 'DDMMYYYYHH12MISSAM'), 'HH24'),
           24
         ) AS interval_name,
         value
  FROM   table_name
  UNPIVOT (value FOR interval_name IN (
    interval1  AS 00,
    interval2  AS 01,
    interval3  AS 02,
    interval4  AS 03,
    interval5  AS 04,
    interval6  AS 05,
    interval7  AS 06,
    interval8  AS 07,
    interval9  AS 08,
    interval10 AS 09,
    interval11 AS 10,
    interval12 AS 11,
    interval13 AS 12,
    interval14 AS 13,
    interval15 AS 14,
    interval16 AS 15,
    interval17 AS 16,
    interval18 AS 17,
    interval19 AS 18,
    interval20 AS 19,
    interval21 AS 20,
    interval22 AS 21,
    interval23 AS 22,
    interval24 AS 23
  ))
)
PIVOT (
  MAX(value) FOR interval_name IN (
    00 AS interval24,
    01 AS interval1,
    02 AS interval2,
    03 AS interval3,
    04 AS interval4,
    05 AS interval5,
    06 AS interval6,
    07 AS interval7,
    08 AS interval8,
    09 AS interval9,
    10 AS interval10,
    11 AS interval11,
    12 AS interval12,
    13 AS interval13,
    14 AS interval14,
    15 AS interval15,
    16 AS interval16,
    17 AS interval17,
    18 AS interval18,
    19 AS interval19,
    20 AS interval20,
    21 AS interval21,
    22 AS interval22,
    23 AS interval23
  )
);

Which, for the sample data:

CREATE TABLE table_name (
  customer,
  interval_type,
  data_count,
  Start_time,
  interval1,
  interval2,
  interval3,
  interval4,
  interval5,
  interval6,
  interval7,
  interval8,
  interval9,
  interval10,
  interval11,
  interval12,
  interval13,
  interval14,
  interval15,
  interval16,
  interval17,
  interval18,
  interval19,
  interval20,
  interval21,
  interval22,
  interval23,
  interval24
) AS
SELECT 67891, 60, 5, '06022022040000AM', 0.07, 0.767, 0.65, 0.69, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 FROM DUAL UNION ALL
SELECT 12345, 60, 8, '06022022120000PM', 0.07, 0.767, 0.65, 0.69, 0.767, 0.69, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 FROM DUAL;

Outputs:

CUSTOMER DATA_COUNT START_TIME INTERVAL24 INTERVAL1 INTERVAL2 INTERVAL3 INTERVAL4 INTERVAL5 INTERVAL6 INTERVAL7 INTERVAL8 INTERVAL9 INTERVAL10 INTERVAL11 INTERVAL12 INTERVAL13 INTERVAL14 INTERVAL15 INTERVAL16 INTERVAL17 INTERVAL18 INTERVAL19 INTERVAL20 INTERVAL21 INTERVAL22 INTERVAL23
12345 60 06022022120000PM 0 0 0 0 0 0 0 0 0 0 0 0 .07 .767 .65 .69 .767 .69 0 0 0 0 0 0
67891 60 06022022040000AM 0 0 0 0 .07 .767 .65 .69 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

db<>fiddle here

CodePudding user response:

Here is one way to deal with it. First UNPIVOT the intervals, then index them (COLUMN_NO) and use MODEL clause to shift the values according to DATA_COUNT and finaly PIVOT it back again. Here is the sample with 12 intervals:

WITH
    tbl AS
        (
            SELECT  '67891'                 "CUSTOMER",
                    '60'                    "INTERVAL_TYPE",
                    '5'                     "DATA_COUNT",
                    '06022022040000AM'      "START_TIME",
                    '0.07'                  "INTERVAL1",
                    '0.767'                 "INTERVAL2",
                    '0.65'                  "INTERVAL3",
                    '0.69'                  "INTERVAL4",
                    '0.62'                  "INTERVAL5",
                    '0.61'                  "INTERVAL6",
                    '0.70'                  "INTERVAL7",
                    '0.68'                  "INTERVAL8",
                    '0.62'                  "INTERVAL9",
                    '0.59'                  "INTERVAL10",
                    '0.69'                  "INTERVAL11",
                    '0.60'                  "INTERVAL12"    FROM DUAL UNION ALL
        --
            SELECT  '12345'                 "CUSTOMER",
                    '60'                    "INTERVAL_TYPE",
                    '8'                     "DATA_COUNT",
                    '06022022120000PM'      "START_TIME",
                    '0.07'                  "INTERVAL1",
                    '0.767'                 "INTERVAL2",
                    '0.65'                  "INTERVAL3",
                    '0.69'                  "INTERVAL4",
                    '0.767'                 "INTERVAL5",
                    '0.69'                  "INTERVAL6",
                    '0.70'                  "INTERVAL7",
                    '0.68'                  "INTERVAL8",
                    '0.62'                  "INTERVAL9",
                    '0.59'                  "INTERVAL10",
                    '0.69'                  "INTERVAL11",
                    '0.60'                  "INTERVAL12"    FROM DUAL
        ), 
    datarows AS
        (
            SELECT  CUSTOMER, INTERVAL_TYPE, DATA_COUNT, START_TIME,
                    VALUE_NAME, VALUE_OF
            FROM    tbl
                UNPIVOT (
                            VALUE_OF FOR VALUE_NAME 
                                IN (INTERVAL1, INTERVAL2, INTERVAL3, INTERVAL4, INTERVAL5, INTERVAL6, 
                                    INTERVAL7, INTERVAL8, INTERVAL9, INTERVAL10, INTERVAL11, INTERVAL12)
                        )
        ),
    dataset AS
        (
            SELECT 
                CUSTOMER, INTERVAL_TYPE, DATA_COUNT, START_TIME,
                REPLACE(VALUE_NAME, 'INTERVAL', '') "COLUMN_NO",
                VALUE_NAME,
                VALUE_OF,
                VALUE_OF "ORIG_VALUE"
            FROM 
                datarows
        ),
    combined AS
        (
            SELECT
                CUSTOMER,
                INTERVAL_TYPE,
                DATA_COUNT,
                START_TIME,
                COLUMN_NO,
                VALUE_NAME,
                Nvl(VALUE_OF, '0') "VALUE_OF",
                ORIG_VALUE
            FROM
                dataset
            MODEL
                PARTITION BY (CUSTOMER)
                DIMENSION BY (COLUMN_NO, DATA_COUNT)
                MEASURES (VALUE_OF, VALUE_NAME, INTERVAL_TYPE, START_TIME, ORIG_VALUE)
                    RULES ITERATE(12)
                        (       
                            VALUE_OF[ITERATION_NUMBER   1, ANY]  = CASE 
                                                              WHEN CV(COLUMN_NO) - To_Number(CV(DATA_COUNT))   1 < 0  - To_Number(CV(DATA_COUNT)) - 1 --CV(COLUMN_NO)
                                                              THEN '0' 
                                                          ELSE 
                                                              ORIG_VALUE[To_Char(To_Number(CV(COLUMN_NO)) - To_Number(CV(DATA_COUNT))   2), CV(DATA_COUNT)]
                                                          END
                        )
        )
SELECT * FROM
(
    SELECT 
        CUSTOMER,
        INTERVAL_TYPE,
        DATA_COUNT,
        START_TIME, 
        VALUE_NAME,
        VALUE_OF
    FROM 
        combined
    WHERE CUSTOMER = '67891'
)
PIVOT(
        MAX(VALUE_OF)
          FOR VALUE_NAME
            IN ('INTERVAL1', 'INTERVAL2', 'INTERVAL3', 'INTERVAL4', 'INTERVAL5', 'INTERVAL6', 
              'INTERVAL7', 'INTERVAL8', 'INTERVAL9', 'INTERVAL10', 'INTERVAL11', 'INTERVAL12')
    )
UNION ALL
SELECT * FROM
    (
        SELECT 
            CUSTOMER,
            INTERVAL_TYPE,
            DATA_COUNT,
            START_TIME, 
            VALUE_NAME,
            VALUE_OF
        FROM 
            combined
        WHERE CUSTOMER = '12345'
    )
    PIVOT(
            MAX(VALUE_OF)
              FOR VALUE_NAME
                IN ('INTERVAL1%' AS INTERV1, 'INTERVAL2', 'INTERVAL3', 'INTERVAL4', 'INTERVAL5', 'INTERVAL6', 
                  'INTERVAL7', 'INTERVAL8', 'INTERVAL9', 'INTERVAL10', 'INTERVAL11', 'INTERVAL12')
        )
--      
--      
--  CUSTOMER INTERVAL_TYPE DATA_COUNT START_TIME       'INTERVAL1' 'INTERVAL2' 'INTERVAL3' 'INTERVAL4' 'INTERVAL5' 'INTERVAL6' 'INTERVAL7' 'INTERVAL8' 'INTERVAL9' 'INTERVAL10' 'INTERVAL11' 'INTERVAL12'
--  -------- ------------- ---------- ---------------- ------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------ ------------ ------------
--  67891    60            5          06022022040000AM         0           0           0.07        0.767       0.65        0.69        0.62        0.61        0.70         0.68         0.62         
--  12345    60            8          06022022120000PM         0           0           0           0           0           0.07        0.767       0.65        0.69         0.767        0.69

Hopefully, you could use it to solve the problem. Regards...

  • Related