1, rollup and cube functions, automatically summarize data select * from TEST_TBL data such
Col_a Col_b Col_c
---- ----- -----
1 B1 12
1 B1 2
1 B2 31
2 B2 7
2 B3 42
2 B3 1
2 B3 3
If columns A and B are summarized in column C, the general approach is this: select Col_a,col_b,sum (Col_c) fromtest_tbl GROUP by Col_a,col_b results are as follows
Col_a col_b sum (Col_c)
---- ----- --------
1 B1 14
1 B2 31
2 B2 7
2 B3 46
But if you want to press the column A and the total number of columns in column C, then you need to use two SQL to nest, it is cumbersome, but with rollup is much simpler:
Select NVL (col_a, ' total ') COL_A,NVL (Col_b,decode (Col_a,null, ', ' Subtotal ' | | col_a)) Col_b,sum (Col_c)
From TEST_TBL group Byrollup (col_a,col_b), the results are as follows
Col_a col_b sum (Col_c)
---- ----- --------
1 B1 14
1 B2 31
1 Subtotal 1 45
2 B2 7
2 B3 46
2 Subtotal 2 53
Total 98
The result set happens to be summarized by a and B, then by a, then by the sum of all, and then if you want to summarize by column B again, what should I do? Do you want to use SQL nesting again? No, if this is required, use the cube function to OK SELECTNVL (Col_a,decode (col_b,null, ' total ', ' Subtotal ' | | Col_b) Col_a,nvl (Col_b,decode (Col_a,null, "," Subtotal "| | col_a)) Col_b,sum (Col_c)
The results from the TEST_TBL Group by Cube (Col_a,col_b) are as follows
Col_a col_b sum (Col_c)
---- ----- --------
1 B1 14
1 B2 31
1 Subtotal 1 45
2 B2 7
2 B3 46
2 Subtotal 2 53
Subtotal B1 B1 14
Subtotal B2 B2 38
Subtotal B3 B3 46
Total 98
It's a bit different from the result set of the rollup function just now, which is that there are a few more rows that are grouped by column B. 2. Lag and lead function, automatic link up/down record value sql> desctest_tbl
Name Type
----- ------
Col_k number
Now insert a series of data into this TEST_TBL table sequentially, below the SQL:
INSERT into TEST_TBL values (1)
INSERT into TEST_TBL values (2)
INSERT into TEST_TBL values (4)
INSERT into TEST_TBL values (5)
INSERT into TEST_TBL values (8)
INSERT into TEST_TBL values (9)
INSERT into TEST_TBL values (11)
INSERT into TEST_TBL values (12)
INSERT into TEST_TBL values (13)
........
After the data is plugged in, to check the inserted data, from the minimum number to the maximum number of those numbers is not inserted into the table, find out the number of the previous and the last number? In this example, from 1 to 13, there are 3, 6, 7, 10 are not inserted in the table, the first and the last of these numbers are 2 and 4, 5 and 8, 9 and 11, i.e.
Prev_val Next_val
---------- ----------
2 4
5 8
9 11
If you don't have to parse the function to get this post-result set, I can't imagine what it's like. A SQL, but the LAG analysis function that's simple, so write OK
Select Prev_val,next_val from (
Select Col_k Next_val,lag (col_k,1,0) over (order by Col_k) Prev_val from Test_tbl
) wherenext_val-prev_val>1
The lead function is the same, except that it is a backward link. 3, rank and Dense_rank function, the Data ranking test table is such a select *fromtest_tbl the following
Col_a Col_b
---------- ----------
A 242
A 233
B 154
C 287
C 76
D 66
E 154
F 154
G 212
G 43
Press column A to count the values of column B, in general SQL is so select Col_a,sum (col_b) from TEST_TBL group bycol_a ORDER BY 2 desc result is this
Col_a SUM (Col_b)
---------- ----------
A 475
C 363
G 255
B 154
F 154
E 154
D 66
From this data set can be seen that a is the largest, C is the second largest, when the data is not known who is the number of rows, then use Dense_rank can achieve this purpose
Select Col_a,sum (Col_b), Dense_rank () over (order by sum (COL_B) desc) ranks Fromtest_tbl GROUP by col_a the results are as follows
Col_a SUM (col_b) ranks
---------- ---------- ----------
A 475 1
C 363 2
G 255 3
B 154 4
F 154 4
E 154 4
D 66 5
This data set ranks each value, and can be directly seen, the same value of the position is the same.
Rank is similar to Dense_rank, but it is when the rank is the same, the next place jumps.
Select Col_a,sum (col_b), rank () over (as of sum (COL_B) desc) ranks from Test_tblgroup by col_a the results are as follows
Col_a SUM (col_b) ranks
---------- ---------- ----------
A 475 1
C 363 2
G 255 3
B 154 4
F 154 4
E 154 4
D 66 7
You can see that the ranking jumps from 4 to 7, because the ranking 4 repeats two times in practice may be more complex than these examples, you may first group the table data, and then use the analysis, such as
The result of select *from Test_tbl is this
Col_g col_a Col_b
---------- ---------- ----------
G1 A 242
G1 A 233
G2 C 287
G2 C 76
G2 D 66
G2 E 154
G3 F 154
G3 G 212
G3 G 43
G2 B 154
To rank this dataset by column G and column A, you would first group the table by the G column and then rank the column-B values by column A.
Select Col_g,col_a,sum (Col_b), Dense_rank () over (partition by Col_g ORDER by sum (COL_B) desc) ranks
From Test_tbl
GROUP BY col_g,col_a This SQL plus Partitionby First GROUP by the G column, the result is as follows
Col_g col_a SUM (col_b) ranks
---------- ---------- --------------------
G1 A 475 1
G2 C 363 1
G2 B 154 2
G2 E 154 2
G2 D 66 3
G3 G 255 1
G3 F 154 2
You can see that the rankings are changed in Group G, and the new arrangement is restarted.
;
grouping and summing SQL example
