基于SQL的数据分析--

1、SUM()select S#fromSCwhereGRADE = max(GRADE)select S#fromSCwhereGRADE = (select max(GRADE)from SC)Grouping ValuesPartitioned TableSum()Aggregate Values列出每个学生的平均成绩列出每门课程的平均成绩group by S#group by C#928590929290 SALES Model Year Color Sales Chevy 1990 red 5 Chevy 1990 white 87 Chevy 1990 blue 62 Chevy 19

2、91 red 54 Chevy 1991 white 95 Chevy 1991 blue 49 Chevy 1992 red 31 Chevy 1992 white 54 Chevy 1992 blue 71 Ford 1990 red 64 Ford 1990 white 62 Ford 1990 blue 63 Ford 1991 red 52 Ford 1991 white 9 Ford 1991 blue 55 Ford 1992 red 27 Ford 1992 white 62 Ford 1992 blue 39Chevy销售的交叉表销售的交叉表Chevy 19941995 to

3、tal (ALL)black 5085135white40115155 total (ALL)90200290Ford销售的交叉表销售的交叉表Ford 19941995 total (ALL)black 5085135white107585 total (ALL)60160220M T W T F S S AIRHOTELFOODMISCSales SummaryModelYearColorUnitsChevy 1994black50Chevy 1994white40Chevy 1994ALL90Chevy 1995black85Chevy 1995white115Chevy 1995ALL2

4、00Chevy ALLALL290SELECT Model, Year, Color, SUM(Sales)FROM SalesWHERE Model = ChevyGROUP BY Model, Year, ColorUNION SELECT Model, Year, ALL, SUM(Sales)FROM SalesWHERE Model = ChevyGROUP BY Model, YearUNIONSELECT Model, ALL, ALL, SUM(Sales)FROM SalesWHERE Model = ChevyGROUP BY Model DATA CUBE Model Y

5、ear Color Sales ALL ALL ALL 942 chevy ALL ALL 510 ford ALL ALL 432 ALL 1990 ALL 343 ALL 1991 ALL 314 ALL 1992 ALL 285 ALL ALL red 165 ALL ALL white 273 ALL ALL blue 339 chevy 1990 ALL 154 chevy 1991 ALL 199 chevy 1992 ALL 157 ford 1990 ALL 189 ford 1991 ALL 116 ford 1992 ALL 128 chevy ALL red 91 che

6、vy ALL white 236 chevy ALL blue 183 ford ALL red 144 ford ALL white 133 ford ALL blue 156 ALL 1990 red 69 ALL 1990 white 149 ALL 1990 blue 125 ALL 1991 red 107 ALL 1991 white 104 ALL 1991 blue 104 ALL 1992 red 59 ALL 1992 white 116 ALL 1992 blue 110 SALES Model Year Color Sales Chevy 1990 red 5 Chev

7、y 1990 white 87 Chevy 1990 blue 62 Chevy 1991 red 54 Chevy 1991 white 95 Chevy 1991 blue 49 Chevy 1992 red 31 Chevy 1992 white 54 Chevy 1992 blue 71 Ford 1990 red 64 Ford 1990 white 62 Ford 1990 blue 63 Ford 1991 red 52 Ford 1991 white 9 Ford 1991 blue 55 Ford 1992 red 27 Ford 1992 white 62 Ford 199

8、2 blue 39CUBE总行数= (model个数+1) * (theyear个数+1) * (color个数+1) = (2 + 1) * (3 + 1) * (3 + 1) = 48CHEVY FORD1990199119921993REDWHITEBLUEByColorByMake&ColorByMake&YearByColor&YearByMakeByYearSumThe Data Cube and The Sub-Space AggregatesSumREDWHITEBLUEChevy FordByMakeByColorCross TabREDWHITEBL

9、UEByColorSumGroup By (with total)SumAggregateSELECT SUM(units_sold), model, theyear, color FROM my_cube GROUP BY model, theyear, color WITH CUBESELECT SUM(units_sold), CASE WHEN (GROUPING(model)=1) THEN ALLELSE ISNULL(model, ?)END,CASE WHEN (GROUPING(theyear)=1) THEN ALLELSE ISNULL(theyear, ?)END, C

10、ASE WHEN (GROUPING(color)=1) THEN ALLELSE ISNULL(color, ?)ENDFROM my_cubeGROUP BY model, theyear, color WITH CUBESELECT unit_sold = SUM(units_sold), model=CASE WHEN (GROUPING(model)=1) THEN ALLELSE ISNULL(model, ?)END, theyear= CASE WHEN (GROUPING(theyear)=1) THEN ALLELSE ISNULL(theyear, ?)END, colo

11、r= CASE WHEN (GROUPING(color)=1) THEN ALLELSE ISNULL(color, ?)ENDFROM my_cubeGROUP BY model, theyear, color WITH ROLLUP总行数= (color个数+1)* theyear个数+1) * model个数+1Skyline：更高、更靠近河流的建筑SELECT WORKDEPT, LASTNAME, SALARY, DECIMAL(SALARY, 15, 0) * 100 / SUM(SALARY)OVER (PARTITION BY WORKDEPT) AS DEPT_SALARY

12、_PERCENTFROM EMPLOYEEWHERE WORKDEPT IN (A00, A11, B01, C01, D1, D11)ORDER BY WORKDEPT, DEPT_SALARY_PERCENT DESC相同相同 manager_id 人员的平均工资人员的平均工资SELECT manager_id, last_name, hire_date, salary, AVG(salary) OVER (PARTITION BY manager_id) AS c_mavgFROM employeesMANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG

13、100 Kochhar 1989-9-21 17000 11100100De Haan 1993-1-13 17000 11100100 Raphaely 1994-12-7 11000 11100101 Whalen 1987-9-17 4400 8980101Mavris 1994-6-7 6500 8980101 Higgins 1994-6-7 12000 8980101 Baer 1994-6-710000 8980按雇佣日期排序，每行数据与前面所有行的平均工资按雇佣日期排序，每行数据与前面所有行的平均工资SELECT manager_id, last_name, hire_date

14、, salary, AVG(salary) OVER (ORDER BY hire_date) AS c_mavgFROM employeesMANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG King 1987-6-17 24000 24000101 Whalen 1987-9-17 4400 14200100 Kochhar 1989-9-21 17000 15133102 Hunold 1990-1-3 9000 13600103 Ernst 1991-5-21 6000 12080按雇佣日期排序，每行数据与前面一行和后面一行的平均工资按雇佣日期排序

15、，每行数据与前面一行和后面一行的平均工资SELECT manager_id, last_name, hire_date, salary, AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavgFROM employeesMANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVGKing 1987-6-17 24000 14200101 Whalen 1987-9-17 4400 15133100 Kochhar 1989-9-21 17000 10133102 Hunold 1990-1-3 9000 10666103 Ernst 1991-5-21 6000 10666查询本人工资以及和本人工资差距在100内的员工个数select ename, sal, greater_num+lower_num from (select ename, sal, count(ename) over ( order by sal desc range 100 preceding) as greater_num , (count(