Oracle grouped row counts to columns

Question

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?

Answer 1

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).

Answer 2

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.