2

I have a table which contains this data:

- ID - PRIMARY KEY, INTEGER
- PC_ID - INTEGER
- USER_ID - INTEGER
- PAYMENT_DATETIME - TIMESTAMP
- FIO - VARCHAR2(255)

When I do a SELECT I get this (example):

1, 1, 1, 08.06.2018 17:33:26, Test
2, 1, 1, 08.06.2018 17:34:52, Test
3, 1, 1, 08.06.2018 18:01:33, Test
4, 1, 1, 08.06.2018 18:03:17, Test2
5, 1, 1, 08.06.2018 19:26:41, Test
6, 1, 1, 09.06.2018 13:22:58, Test2

So if I group them by PC_ID, USER_ID, Distinct FIO, DATE (DD. MM. YYYY), TIME (HH24) as rows to show amount of different FIO for hours 07 to 22 for each day for each user for each pc, I get:

1, 1, 08.06.2018, 17, 1
1, 1, 08.06.2018, 18, 2
1, 1, 08.06.2018, 19, 1
1, 1, 09.06.2018, 13, 1

The last column is the Count of distinct FIO.

Now I would like to transform it to:

Columns: PC_ID, USER_ID, DATE (DD. MM. YYYY), TIME (HH24) for 07 to 22 and TOTAL FOR DATE

If the count for hour is 0 then show 0:

So the structure should look like:

PC_ID, USER_ID, DATE, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, TOTAL_FIO

I was thinking of CTEs, can this be achieved using them?

2
  • If i understand your question correctly you may want to look at Oracle's Pivot clause. Jun 8, 2018 at 17:24
  • I would appreciate a solution. Jun 9, 2018 at 5:57

2 Answers 2

3

Possible with a CTE, but PIVOT is the easier implementation.

If for total_fio you still wanted distinct, this would work:

SELECT * FROM
 (SELECT pc_id, 
         user_id, 
         to_char(payment_datetime,'DD.MM.YYYY') AS "date", 
         To_char(payment_datetime, 'HH24') AS "hour",
         fio,
         count(DISTINCT fio) OVER ( PARTITION BY  to_char(payment_datetime,'DD.MM.YYYY') ) AS total_fio
  FROM   foobar 
  WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22' )
PIVOT
 (Count(DISTINCT fio)
   FOR "hour" IN ('07', '08', '09', '10', '11', '12', 
                  '13', '14', '15', '16', '17', '18', 
                  '19', '20', '21', '22'));

Or if you wanted a sum of all hour columns, I'm not sure of an elegant way to do it without getting into PL/SQL:

SELECT x.*, "07" + "08" + "09" + "10" + "11" + "12" 
             + "13" + "14" + "15" + "16" + "17" + "18" 
             + "19" + "20" + "21" + "22" AS total_fio
FROM
(SELECT pc_id, 
        user_id, 
        to_char(payment_datetime,'DD.MM.YYYY') AS date_day, 
        To_char(payment_datetime, 'HH24') AS date_hour,
        fio
 FROM   foobar 
 WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22')
PIVOT
(Count(DISTINCT fio)
  FOR date_hour IN ('07' AS "07", '08' AS "08", '09' AS "09", '10' AS "10", '11' AS "11", 
                    '12' AS "12", '13' AS "13", '14' AS "14", '15' AS "15", '16' AS "16", 
                    '17' AS "17", '18' AS "18", '19' AS "19", '20' AS "20", '21' AS "21", 
                    '22' AS "22")) x;

I'm new on here so don't have enough rep to comment on your implementation, but it's a solid go at it for sure. I don't see anything wrong where you'd get inconsistent results. As for optimization, `PIVOT' does the grouping for you. So by using the CTE with the additional group by to start out, it is almost doubling the work the query has to do. My second query above is essentially an optimized version of yours (they are very similar).

4
  • 1
    That's how I'd have approached this (upvoted!). 2 things, though: {1} do we really need the WHERE clause? {2} the "total_fio" column/values are missing.
    – stefan
    Jun 9, 2018 at 10:14
  • Ah thanks much. Missed the total_fio field, will edit. As for where, yeah not needed with this example dataset, but was assuming from his specifications that the real dataset contains times/hours outside the desired range.
    – Mallorie
    Jun 9, 2018 at 10:23
  • What index is it best to have to fasten the select? 400 000 payments take 68 seconds, that's too long. Jun 14, 2018 at 6:56
  • An index on the date_hour will change it from a full table access to an index range scan... since that column is so heavily used, it should make a decent difference. For instance, on my data set it'd look like CREATE INDEX foobar_index ON foobar (To_char(payment_datetime, 'HH24') ASC);
    – Mallorie
    Jun 14, 2018 at 7:31
0

Prior to a single answer to my question I read a little about Pivot clause and made this query:

WITH PIVOT_DATA AS 
(
    SELECT 
    PC_ID, 
    USER_ID, 
    TO_DATE(TO_CHAR(PAYMENT_DATETIME,'DD.MM.YYYY'), 'DD.MM.YYYY HH24:MI:SS') as THEDATE, 
    FIO, 
    EXTRACT(HOUR FROM PAYMENT_DATETIME) AS THEHOUR
    FROM PAYMENTS
    GROUP BY PC_ID, 
    USER_ID, 
    TO_DATE(TO_CHAR(PAYMENT_DATETIME,'DD.MM.YYYY'), 'DD.MM.YYYY HH24:MI:SS'), 
    EXTRACT(HOUR FROM PAYMENT_DATETIME), 
    FIO
)
SELECT CLIENTS.*, 
CLIENTS.H7+CLIENTS.H8+CLIENTS.H9
+CLIENTS.H10+CLIENTS.H11+CLIENTS.H12
+CLIENTS.H13+CLIENTS.H14+CLIENTS.H15
+CLIENTS.H16+CLIENTS.H17+CLIENTS.H18
+CLIENTS.H19+CLIENTS.H20+CLIENTS.H21+CLIENTS.H22 AS THETOTAL
FROM PIVOT_DATA
PIVOT 
(
  COUNT(DISTINCT FIO)
  FOR THEHOUR
  IN (7 H7,8 H8,9 H9,10 H10,11 H11,12 H12,13 H13,14 H14,15 H15,16 H16,17 H17,18 H18,19 H19,20 H20,21 H21,22 H22) 
) CLIENTS;

Is it okay, or are there any serious problems in it? It works and gives the results I need, but it is rather big and I doubt it's optimized.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.