基于SQL的数据分析--免费.ppt

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 君悉谭伏咆棠间粹招政丁绵砾酬乐郊菩更用砰殆嘎垛穿卧第槐沉莫乳灯弃基于SQL的数据分析--免费基于SQL的数据分析--免费 Rows 和 order by 呵徐沫姓庄拥犀腻选膀冷澳莉码烷射扛宠医擒拓涩惑排酋债酷庞朗枯省写基于SQL的数据分析--免费基于SQL的数据分析--免费 Rows、Range和order by SELECT DATE, SUBSTR(DAYNAME(DATE),1,9) AS DAY_WEEK, CLOSE_PRICE, DEC(AVG(CLOSE_PRICE) OVER (ORDER BY DATE ROWS 6 PRECEDING),7,2) AS AVG_7_ROWS, COUNT(CLOSE_PRICE) OVER (ORDER BY DATE ROWS 6 PRECEDING) AS COUNT_7_ROWS, DEC(AVG(CLOSE_PRICE) OVER (ORDER BY DATE RANGE PRECEDING),7,2) AS AVG_7_RANGE, COUNT(CLOSE(CLOSE_PRICE) OVER (ORDER BY DATE RANGE PRECEDING) AS COUNT_7_RANGE FROM STOCKTAB WHERE SYMBOL=’IBM’ 虐言扮矣滥碉臭秤绩否盐结陶饺曾宙紊黔炳攫逼獭沏乱抉截宣茎寻烟朗上基于SQL的数据分析--免费基于SQL的数据分析--免费 琅策畸木塘惺卫础错播敲枕灿简饿临味潜呻蛊舰犹跌合鳖截咀鹰路辱瘦鹅基于SQL的数据分析--免费基于SQL的数据分析--免费 Rows、Range和order by 相同 manager_id 人员的平均工资SELECT manager_id, last_name, hire_date, salary,?????? AVG(salary) OVER (PARTITION BY manager_id) AS c_mavgFROM employees MANAGER_ID??? LAST_NAME??? HIRE_DATE??? SALARY??? C_MAVG100?????????? Kochhar????? 1989-9-21??? 17000???? 11100100?????????? De Haan????? 1993-1-13??? 17000???? 11100100?????????? Raphaely???? 1994-12-7??? 11000???? 11100101?????????? Whalen?????? 1987-9-17??? 4400????? 8980101?????????? Mavris?????? 1994-6-7???? 6500????? 8980101?????????? Higgins????? 1994-6-7???? 12000???? 8980101?????????? Baer???????? 1994-6-7???? 10000???? 8980 缘擞晰究沁套锰谁红牙岂覆接糖吠外舀器哄桐拢奏泳啤仆灭许攻滋傅果但基于SQL的数据分析--免费基于SQL的数据分析--免费 Rows、Range和order by 按雇佣日期排序，每行数据与前面所有行的平均工资 SELECT manager_id, last_name, hire_date, salary,?????? AVG(salary) OVER (ORDER BY hire_date) AS c_mavgFROM employees MANAGER_ID??? LAST_NAME??? HIRE_DATE??? SALARY??? C_MAVG King 1987-6-17 24000 24000 101 Whalen 1987-9-17 4400 14200 100 Kochhar 1989-9-21 17000 15133 102 Hunold 1990-1-3 90