Oracle three practical statistics SQL scenarios, oracle statistics SQL scenarios

Oracle three practical statistics SQL scenarios, oracle statistics SQL scenarios

When using oracle for statistics, we often encounter the following scenarios:

1. Convert vertical columns to horizontal Columns

2. Group and merge a column as the result set

3. Obtain the first record for grouping and sorting

 

We useSimplifiedTo demonstrate how to use SQL to solve these three scenarios.

Business scenario: one table records employees' attendance records

Business Requirements: (corresponding to the preceding three scenarios)

1. count the number of monthly attendance records of employees in a certain year

2. query the attendance records of each person

3. obtain the attendance records for each employee on the first day of work.

 

First, create a test data table and test data.

 

SQL code

1. -- Create an attendance record table
2. Create table T_ATTENDANCE_LOG
3. (
4. Id_varchar (36 ),
5. USERNAME _ VARCHAR (255 ),
6. LOGDATE _ VARCHAR (100)
7. )
10. -- Initialize some test data
11. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('1', 'zhang san', '2017-02-01 ');
12. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('2', 'zhang san', '2017-02-02 ');
13. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('3', 'zhang san', '2017-02-03 ');
14. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('4', 'zhang san', '2017-02-04 ');
15. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('5', 'zhang san', '2017-02-05 ');
16. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('6', 'zhang san', '2017-02-06 ');
18. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('11', 'lily', '2017-03-01 ');
19. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('12', 'lily', '2017-04-01 ');
20. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('13', 'lily', '2017-05-01 ');
22. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('21', '王', '2017-02-15 ');
23. Insert into T_ATTENDANCE_LOG (ID _, USERNAME _, LOGDATE _) VALUES ('22', '王', '2017-03-15 ');
25. -- Query
26. Select t. *, T. rowid from T_ATTENDANCE_LOG T;

 

Result:

 

 

1. Count the monthly attendance of employees in 2014

SQL code

1. With sql1
2. (
3. Select USERNAME _, substr (LOGDATE _, 0, 7) as a, count (LOGDATE _) as B from T_ATTENDANCE_LOG
4. Group by USERNAME _, substr (LOGDATE _, 0, 7)
5. )
6. Select USERNAME _,
7. Sum (case A when '2014-01 'then B end) August 1, 2014,
8. Sum (case A when '2014-02 'then B end) February 2014,
9. Sum (case A when '2014-03 'then B end) February 2014,
10. Sum (case A when '2017-04 'then B end) August 1, 2014,
11. Sum (case A when '2017-05 'then B end) February 2014,
12. Sum (case A when '2017-06 'then B end) February 2014,
13. Sum (case A when '2014-07 'then B end) February 2014,
14. Sum (case A when '2017-08 'then B end) February 2014,
15. Sum (case A when '2014-09 'then B end) August 1, 2014,
16. Sum (case A when '2017-10' then B end) February 2014,
17. Sum (case A when '2017-11' then B end) February 2014,
18. Sum (case A when '2017-12' then B end) August 1, 2014
19. From sql1 group by USERNAME _

Here we use the "SQL statistics Tool" --.

 

Result:

 

 

2. query the attendance records of each person

SQL code

1. Select USERNAME _ as employee, wmsys. wm_concat (LOGDATE _) as attendance record from T_ATTENDANCE_LOG t group by USERNAME _

 

Result:

 

However, we found that the statistical results are out of order.

SQL code

1. Select USERNAME _ as employee, max (r) as attendance record from (
2. Select USERNAME _, wmsys. wm_concat (LOGDATE _) OVER (partition by username _ order by logdate _) r
3. From T_ATTENDANCE_LOG t
4. ) Group by USERNAME _

 

Transformation Result:

 

 

3. obtain the attendance records for each employee on the first day of work.

SQL code

1. SELECT * FROM
2. (
3. -- Group sorting and serial number
4. Select USERNAME _, LOGDATE _, ROW_NUMBER () OVER (partition by username _ order by logdate _) r
5. From T_ATTENDANCE_LOG t
6. Group by USERNAME _, LOGDATE _
7. ) Where R = 1

 

Result: