Navigation Bar

Saturday, November 4, 2023

Types of Queries - II

Hierarchy Query SYS_CONNECT_BY_PATH 

Usage 
SYS_CONNECT_BY_PATH(column, char) 
SYS_CONNECT_BY_PATH is used only in hierarchy queries. It returns a path of a column from root to node with column values separated by char for each row returned by the CONNECT BY condition. Below example is to get a comma ',' separated list of values for an id, in a table where there are multiple values for each id. Here we can use a combination of hierarchy query and analytical function. For in depth of analytical functions I will be writing in my next post. For the purpose of this post we will use the row_number() over and count(*) over features of analytical function. 
CREATE TABLE TBL_LIST
(
id    NUMBER,
val   VARCHAR2(10)
);

insert into TBL_LIST VALUES (1,'AAAAA');
insert into TBL_LIST VALUES (1,'BBBBB');
insert into TBL_LIST VALUES (1,'CCCCC');
insert into TBL_LIST VALUES (1,'DDDDD');
insert into TBL_LIST VALUES (1,'EEEEE');

insert into TBL_LIST VALUES (2,'FFFFF');
insert into TBL_LIST VALUES (2,'GGGGG');
insert into TBL_LIST VALUES (2,'HHHHH');
For each id, we get its row number and count of total values for the id. 
SELECT ID, (VAL),
                                  ROW_NUMBER () OVER (PARTITION BY ID ORDER BY 1) rn,
                                  COUNT (*) OVER (PARTITION BY ID) cnt
                             FROM TBL_LIST
                            WHERE id IN  (1,2)
output as below 
1	AAAAA	1	5
1	BBBBB	2	5
1	CCCCC	3	5
1	DDDDD	4	5
1	EEEEE	5	5
2	FFFFF	1	3
2	GGGGG	2	3
2	HHHHH	3	3
This is the output of the inner query. We now use the hierarchy query SYS_CONNECT_BY_PATH and CONNECT BY conditions. We connect each row with its previous row with the following condition, starting with row having row number = 1. START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1. This mean the current row number = prior rumber + 1 so we get 1 = 1 2 = 1+1 3 = 2 + 1 4 = 3+1 till all the values for that id are looped through. Here is the final query 
SELECT  a.ID, TRIM(',' FROM SUBSTR (
                             SYS_CONNECT_BY_PATH (a.VAL ,
                                                  ','),
                             2)) as tree
                     FROM (SELECT ID, (VAL),
                                  ROW_NUMBER () OVER (PARTITION BY ID ORDER BY 1) rn,
                                  COUNT (*) OVER (PARTITION BY ID) cnt
                             FROM TBL_LIST
                         --   WHERE id IN  (1,2)
                            ) a
                    WHERE 1=1
               AND rn = cnt
               START WITH rn = 1 
               CONNECT BY rn = PRIOR rn + 1 AND ID = PRIOR ID
               ORDER BY ID;
Output 
1	AAAAA,BBBBB,CCCCC,DDDDD,EEEEE
2	FFFFF,GGGGG,HHHHH
To understand the working of this query more clearly we will comment out the following condition rn=cnt Now we will get all the records in the hierarchy. To get the order of rows in the hierarchy we add the LEVEL condition in select and a padding to indent in record as per its LEVEL in the query. 
SELECT  a.ID, LEVEL,  LPAD(' ', 2*level-1)|| TRIM(',' FROM SUBSTR (
                             SYS_CONNECT_BY_PATH (a.VAL ,
                                                  ','),
                             2)) as tree
                     FROM (SELECT ID, (VAL),
                                  ROW_NUMBER () OVER (PARTITION BY ID ORDER BY 1) rn,
                                  COUNT (*) OVER (PARTITION BY ID) cnt
                             FROM TBL_LIST
                         --   WHERE id IN  (1,2)
                            ) a
                    WHERE 1=1
     
--               AND rn = cnt
               START WITH rn = 1 
               CONNECT BY rn = PRIOR rn + 1 AND ID = PRIOR ID
               ORDER BY ID, length(tree);
Output 
1	1	 AAAAA
1	2	   AAAAA,BBBBB
1	3	     AAAAA,BBBBB,CCCCC
1	4	       AAAAA,BBBBB,CCCCC,DDDDD
1	5	         AAAAA,BBBBB,CCCCC,DDDDD,EEEEE
2	1	 FFFFF
2	2	   FFFFF,GGGGG
2	3	     FFFFF,GGGGG,HHHHH
With this example hope the SYS_CONNECT_BY_PATH in hierarchy queries is more clearly understood.

Pivot Query
A pivot query helps to summarize the data in a table. It can be grouped or aggregated to focus on a particular aspect of data or trends in data. It is useful to summarize large amounts of data as aggregates like sums, averages, maximum etc to get a broader picture of the data to analyze trends in data.
For example
There is a shop TRONIC CORPORATION LTD which sells devices like mobiles, laptops, televisions etc. The database stores the day wise, modelwise sale of these gadgets. If this sales data (day wise and device model wise count) is displayed in a grid format with the days on X axis and the device and model on Y axis, the analyst can get a more holistic view of the data for further analysis.
Below is the sample table data and query for the same.

CREATE TABLE TRONIC_SALE_STATS
(
  SALE_DATE   DATE,
  DEVICE       VARCHAR2(100),
  DEVICE_MODEL VARCHAR2(100),
  PRICE       NUMBER,
  QTY         NUMBER
);

INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'LAPTOP', 'ULTRA',185000,12);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'LAPTOP', 'PREMIUM',95000,24);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'MOBILE', 'ULTRA',125000,72);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'MOBILE', 'PREMIUM',75000,120);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'MOBILE', 'STANDARD',45000,95);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'TV', 'ULTRA',225000,10);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'TV', 'PREMIUM',120000,35);
INSERT INTO TRONIC_SALE_STATS VALUES('01-MAY-23', 'TV', 'STANDARD',75000,65);

INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'LAPTOP', 'ULTRA',185000,10);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'LAPTOP', 'PREMIUM',95000,21);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'MOBILE', 'ULTRA',125000,68);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'MOBILE', 'PREMIUM',75000,115);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'MOBILE', 'STANDARD',45000,89);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'TV', 'ULTRA',225000,7);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'TV', 'PREMIUM',120000,28);
INSERT INTO TRONIC_SALE_STATS VALUES('02-MAY-23', 'TV', 'STANDARD',75000,61);

INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'LAPTOP', 'ULTRA',185000,7);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'LAPTOP', 'PREMIUM',95000,18);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'LAPTOP', 'STANDARD',65000,78);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'MOBILE', 'ULTRA',125000,55);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'MOBILE', 'PREMIUM',75000,84);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'MOBILE', 'STANDARD',45000,62);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'TV', 'ULTRA',225000,4);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'TV', 'PREMIUM',120000,21);
INSERT INTO TRONIC_SALE_STATS VALUES('03-MAY-23', 'TV', 'STANDARD',75000,45);

INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'LAPTOP', 'ULTRA',185000,2);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'LAPTOP', 'PREMIUM',95000,10);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'LAPTOP', 'STANDARD',65000,45);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'MOBILE', 'ULTRA',125000,35);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'MOBILE', 'PREMIUM',75000,42);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'MOBILE', 'STANDARD',45000,47);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'TV', 'ULTRA',225000,1);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'TV', 'PREMIUM',120000,12);
INSERT INTO TRONIC_SALE_STATS VALUES('04-MAY-23', 'TV', 'STANDARD',75000,25);


INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'LAPTOP', 'PREMIUM',95000,3);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'LAPTOP', 'STANDARD',65000,30);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'MOBILE', 'ULTRA',125000,12);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'MOBILE', 'PREMIUM',75000,32);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'MOBILE', 'STANDARD',45000,26);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'TV', 'PREMIUM',120000,4);
INSERT INTO TRONIC_SALE_STATS VALUES('05-MAY-23', 'TV', 'STANDARD',75000,19);
Normal Output:

SALE_DATE DEVICE MODEL     PRICE    QTY

01-MAY-23 LAPTOP ULTRA 185000 12
01-MAY-23 LAPTOP PREMIUM 95000 24
01-MAY-23 MOBILE ULTRA 125000 72
01-MAY-23 MOBILE PREMIUM 75000 120
01-MAY-23 MOBILE STANDARD  45000 95
01-MAY-23 TV ULTRA 225000 10
01-MAY-23 TV PREMIUM 120000 35
01-MAY-23 TV STANDARD 75000 65
02-MAY-23 LAPTOP ULTRA 185000 10
02-MAY-23 LAPTOP PREMIUM 95000 21
02-MAY-23 MOBILE ULTRA 125000 68
02-MAY-23 MOBILE PREMIUM 75000 115
02-MAY-23 MOBILE STANDARD 45000 89
02-MAY-23 TV ULTRA 225000 7
02-MAY-23 TV PREMIUM 120000 28
02-MAY-23 TV STANDARD 75000 61
03-MAY-23 LAPTOP ULTRA 185000 7
03-MAY-23 LAPTOP PREMIUM 95000 18
03-MAY-23 LAPTOP STANDARD 65000 78
03-MAY-23 MOBILE ULTRA 125000 55
03-MAY-23 MOBILE PREMIUM 75000 84
03-MAY-23 MOBILE STANDARD 45000 62
03-MAY-23 TV ULTRA 225000 4
03-MAY-23 TV PREMIUM 120000 21
03-MAY-23 TV STANDARD 75000 45
04-MAY-23 LAPTOP ULTRA 185000 2
04-MAY-23 LAPTOP PREMIUM 95000 10
04-MAY-23 LAPTOP STANDARD 65000 45
04-MAY-23 MOBILE ULTRA 125000 35
04-MAY-23 MOBILE PREMIUM 75000 42
04-MAY-23 MOBILE STANDARD 45000 47
04-MAY-23 TV ULTRA 225000 1
04-MAY-23 TV PREMIUM 120000 12
04-MAY-23 TV STANDARD 75000 25
05-MAY-23 LAPTOP PREMIUM 95000 3
05-MAY-23 LAPTOP STANDARD 65000 30
05-MAY-23 MOBILE ULTRA 125000 12
05-MAY-23 MOBILE PREMIUM 75000 32
05-MAY-23 MOBILE STANDARD 45000 26
05-MAY-23 TV PREMIUM 120000 4
05-MAY-23 TV STANDARD 75000 19

 
SELECT * FROM
(
  SELECT SALE_DATE,DEVICE || ' ' || DEVICE_MODEL DEVICE,QTY
  FROM TRONIC_SALE_STATS
)
PIVOT
(
  sum(QTY) 
  FOR SALE_DATE IN ('01-May-23', '02-May-23','03-May-23', '04-May-23','05-May-23')
) 
ORDER BY DEVICE;
Pivot Output

DEVICE 01-May-23     02-May-23     03-May-23     04-May-23     05-May-23

LAPTOP PREMIUM 24 21 18 10 3
LAPTOP STANDARD NULL NULL 78 45 30
LAPTOP ULTRA 12 10 7 2 NULL
MOBILE PREMIUM 120 115 84 42 32
MOBILE STANDARD 95 89 62 47 26
MOBILE ULTRA 72 68 55 35 12
TV PREMIUM 35 28 21 12 4
TV STANDARD 65 61 45 25 19
TV ULTRA 10 7 4 1 NULL                   

 
Thought for the day
Dont judge each day by the harvest you reap, but by the seeds that you plant.
--R L Stevenson
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)