MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

涓�銆佽儗鏅�浠嬬�?/h2>

浠婂ぉ锛岄噹楦″ぇ瀛﹂珮锛堜笁锛夌彮鐨勬湀鑰冩垚缁╁嚭鏉ヤ簡锛岃繖閲屽厛缁欏ぇ瀹跺叕甯冧竴涓嬪悇浣嶅悓瀛︾殑鑰冭瘯鎴愮哗銆?/p>

鎺ョ潃锛屽湪缁欏ぇ瀹跺叕甯冧竴涓嬪悇浣嶅悓瀛︾殑鐢熸椿娑堣垂鎯呭喌銆?/p>

iTunes基本使用教程	没墨水就不能扫描，佳能被起诉一体机功能故意设限
详谈如何塑造环境人像光影	怎么进行seo关键词选择

涓嬮潰鎴戜滑鍒╃敤涓婅堪鑰冭瘯鎴愮哗鍜岀敓娲绘秷璐硅�板綍锛屽埄鐢╩ysql鍋氫竴涓�绠�鍗曠殑鍒嗘瀽銆?/p>

褰撶劧锛屼粠鏈�鏂囨爣棰樺氨鍙�浠ョ湅鍑烘潵銆傛湰鏂囧氨鏄�瑕佺粨鍚堣繖浠芥暟鎹�锛屼负澶у�惰�茶堪SQL 鈥滅獥鍙ｅ嚱鏁扳�?搴旇�ユ�庝箞鐢�锛�

鍖呮嫭浣犱互鍚庡�︿範hive鎴栬�卭racle鏁版嵁搴擄紝鎴栬�呰�存暟鎹�鍒嗘瀽闈㈣瘯锛岃繖閮藉皢鏄�涓�涓�寰堥噸瑕佺殑鐭ヨ瘑鐐广�?/p>

浜屻�佸缓琛ㄨ��鍙ュ拰鎻掑叆鏁版嵁

鍒涘缓琛ㄦ牸

create table exam_score(
    sname varchar(20),
    age int,
    subject varchar(20),
    score varchar(20)
)charset=utf8;

# ----------------------- #

create table cost_fee(
    sname varchar(20),
    buydate varchar(20),
    buycost int
)charset=utf8;

鎻掑叆鏁版嵁

insert into exam_score values
('寮犱笁' , 18, '璇�鏂�' , 90),
('寮犱笁' , 18, '鏁板��' , 80),
('寮犱笁' , 18, '鑻辫��' , 70),
('鏉庡洓' , 21, '璇�鏂�' , 88),
('鏉庡洓' , 21, '鏁板��' , 78),
('鏉庡洓' , 21, '鑻辫��' , 71),
('鐜嬩簲' , 18, '璇�鏂�' , 95),
('鐜嬩簲' , 18, '鏁板��' , 83),
('鐜嬩簲' , 18, '鑻辫��' , 71),
('璧靛叚' , 19, '璇�鏂�' , 98),
('璧靛叚' , 19, '鏁板��' , 90),
('璧靛叚' , 19, '鑻辫��' , 80);
# ----------------------- #
insert into cost_fee values
('寮犱笁','2019-01-01',10),
('寮犱笁','2019-03-03',23),
('寮犱笁','2019-02-05',46),
('鏉庡洓','2019-02-02',15),
('鏉庡洓','2019-01-07',50),
('鏉庡洓','2019-03-04',29),
('鐜嬩簲','2019-03-08',62),
('鐜嬩簲','2019-02-09',68),
('鐜嬩簲','2019-01-11',75),
('璧靛叚','2019-02-08',55),
('璧靛叚','2019-03-10',12),
('璧靛叚','2019-01-12',80);

涓夈�佺獥鍙ｅ嚱鏁板垎绫讳粙缁?/h2>

鍦ㄦ�ｅ紡璁茶�?鈥滅獥鍙ｅ嚱鏁扳�?搴旂敤涔嬪墠锛屾垜杩欓噷鍏堝甫鐫�澶у�舵⒊鐞嗕竴閬?鈥滅獥鍙ｅ嚱鏁扳�?鐨勫熀纭�銆傛垜浠�鍙�浠ュ皢绐楀彛鍑芥暟鍒嗕负濡備笅鍑犵被锛?/p>

鑱氬悎鍑芥暟 + over()鎼�閰嶏�?/p>

鎺掑簭鍑芥暟 + over()鎼�閰嶏�?/p>

ntile()鍑芥暟 + over()鎼�閰嶏�?/p>

鍋忕Щ鍑芥暟 + over()鎼�閰嶏�?/p>

鍏蜂綋姣忎竴绫伙紝鏈夊摢浜涘嚱鏁板憿锛熻�傚療涓嬮潰鐨勬�濈淮瀵煎浘銆?/p>

瀵逛簬over()閲岄潰锛岃繖閲岃繕鏈変袱涓�甯哥敤鐨勫叧閿�瀛楋紝蹇呴』瑕佽�茶堪銆傚�備笅锛�

partition by + 瀛楁�碉細浣犲彲浠ユ兂璞℃垚group by鍏抽敭瀛楋紝灏辨槸鐢ㄤ簬 鈥?/strong>鍒嗙粍鈥?鐨勫叧閿�瀛楋�?/p>

order by + 瀛楁�碉細杩欎釜鏇村�规槗鐞嗚В锛屽氨鏄�鐢ㄤ�?鈥滄帓搴忊�?鐨勫叧閿�瀛楋�?/p>

鍥涖�佺獥鍙ｅ嚱鏁板簲鐢?/h2>

涓婇潰缁欏ぇ瀹朵粙缁嶄簡鑻ュ共甯哥敤鐨?鈥滅獥鍙ｅ嚱鏁扳�濓紝杩欓噷鍒╃敤鏂囬�栧垱寤虹殑鏁版嵁锛岃�茶�� 鈥滅獥鍙ｅ嚱鏁扳�?鐨勫簲鐢ㄣ�?/p>

甯屾湜澶у�堕�氳繃姣忎釜妗堜緥锛屾潵鎬荤粨涓�涓嬫瘡涓�鍑芥暟鐨勫惈涔夛紝杩欓噷灏变笉璇︾粏鍐欎簡銆?/p>

1. 鑱氬悎鍑芥暟 + over()鎼�閰�

鈶?璁＄畻姣忎綅鍚屽�︾殑寰楀垎涓庡钩鍧囧�肩殑鎯呭喌

同样用5G网，有人网速快有人慢，为啥？	39岁亿万富豪被抓，背后是“屡教不改”？
基础知识：镜头“等效焦距”是什么，与“焦距”怎么区分？	微软Win11恢复Win+X菜单访问键

select 
	sname
    ,subject
    ,score
    ,avg(score) over(partition by sname) as avg_score
from
	exam_score

缁撴灉濡備笅锛?/p>

鈶?璁＄畻姣忎綅鍚屽��1-3鏈堟秷璐规儏鍐靛拰娑堣垂鎬婚��

select
	sname
    ,buydate
    ,buycost
    ,sum(buycost) over(partition by sname) as sum_cost
from
	cost_fee

缁撴灉濡備笅锛?/p>

鈶?璁＄畻姣忎綅鍚屽��1-3鏈堟秷璐规儏鍐靛拰绱�璁℃秷璐规�婚��

select
	sname
    ,buydate
    ,buycost
    ,sum(buycost) over(partition by sname order by buydate) as sum_cost
from
	cost_fee

缁撴灉濡備笅锛?/p>

娉ㄦ剰锛?缁撳悎鈶♀憿锛屽ぇ瀹跺彲浠ュ彂鐜皃artition by缁撳悎order by锛屼笌涓嶇粨鍚坥rder by锛屽緱鍒扮殑瀹屽叏鏄�涓嶅悓鐨勭粨鏋溿�備竴涓�鏄�鍒嗙粍姹傛�诲拰锛堜笉鍔爋rder by锛夛紱涓�涓�鏄�鍒嗙粍姹傜疮璁″拰锛堝姞order by锛夈�?/p>

华为新款MateBook E二合一笔记本外观曝光	没墨水就不能扫描，佳能被起诉一体机功能故意设限
手感是不是玄学？关于手感的二三事	5分钟告诉你要不要买 iPhone12？

2. 鎺掑簭鍑芥暟 + over()鎼�閰�

鈶?璁＄畻姣忎釜绉戠洰鐨勬帓鍚嶏紝鐩稿悓鐨勫垎鏁版帓鍚嶄笉鍚岋紝椤哄簭渚濇�″�炲姞

select
	sname
	,subject
	,score
    ,row_number() over(partition by subject order by score) rank1
from
	exam_score

缁撴灉濡備笅锛?/p>

鈶?璁＄畻姣忎釜绉戠洰鐨勬帓鍚嶏紝鐩稿悓鐨勫垎鏁版帓鍚嶇浉鍚岋紝浣欎笅鎺掑悕璺宠穬澧炲姞

select
	sname
	,subject
	,score
    ,rank() over(partition by subject order by score) rank1
from
	exam_score

缁撴灉濡備笅锛?/p>

鈶?璁＄畻姣忎釜绉戠洰鐨勬帓鍚嶏紝鐩稿悓鐨勫垎鏁版帓鍚嶇浉鍚岋紝浣欎笅鎺掑悕椤哄簭澧炲姞

select
	sname
	,subject
	,score
    ,dense_rank() over(partition by subject order by score) rank1
from
	exam_score

缁撴灉濡備笅锛?/p>

3. ntile()鍑芥暟 + over()鎼�閰�

ntile()鍑芥暟鏈夌偣涔卞叆鐨勬劅瑙夛紝浣犱笉鐭ラ亾缁欏畠鍒嗗摢涓�绫汇�傝�ュ嚱鏁颁富瑕佺�?鈥?/strong>鏁版嵁鍒囧垎鈥濄�傚�傛灉璇磋繖涓�鍑芥暟杩樻湁鐐圭敤鐨勮瘽锛屽氨鏄�浠栦篃鍙�浠ュ�规暟鎹�杩涜�屾帓搴忥紝绫讳技浜庝笂闈㈡彁鍒扮殑row_number()鍑芥暟銆?/p>

鈶?瀵筫xam_score琛�锛岃繘琛屾暣寮犺〃鍒囧�?/p>

select
	sname
	,subject
	,score
    ,ntile(4) over() rank1
from
	exam_score

缁撴灉濡備笅锛?/p>

涓嶄俊浣犱笅鍘昏瘯涓�涓嬶紝ntile()閲岄潰涓嶇�″啓鍝�涓�鏁板瓧锛屽ソ鍍忛兘鍙�浠ャ�?/p>

鈶?瀵筫xam_score琛�锛屾寜鐓�subject鍒嗙粍鍒囧垎

select
	sname
	,subject
    ,score
    ,ntile(4) over(partition by subject) rank1
from
	exam_score

缁撴灉濡備笅锛?/p>

鍗充娇鏄�鍒嗙粍鍒囧垎锛屼綘涔熶細鍙戠幇锛岃繖鏍锋��鏃犳剰涔夛紝鍥犱负score骞舵病鏈夋帓搴忋�?/p>

鈶?瀵筫xam_score琛�锛屽�箂core鎺掑簭鍚庯紝鎸夌収subject鍒嗙粍鍒囧垎锛堟渶鏈夌敤锛?/p>

select
	sname
	,subject
    ,score
    ,ntile(4) over(partition by subject order by score) rank1
from
	exam_score

缁撴灉濡備笅锛?/p>

娉ㄦ剰锛?浠旂粏瑙傚療杩欑�嶇敤娉曪紝鍩烘湰鍙�浠ョ瓑鏁坮ow_number()鍑芥暟锛屾晥鏋滄槸涓�鏍风殑銆?/p>

4. 鍋忕Щ鍑芥暟 + over()鎼�閰�

鈶?灞曠ず鍚勪綅鍚屽�︾殑鈥滀笂娆¤喘涔版椂闂粹�濆拰鈥滀笅娆¤喘涔版椂闂粹�?/p>

娉�锛氬�逛簬绗�涓�澶╋紝鏄剧ず 鈥渇irst buy鈥濓紱瀵逛簬鏈�鍚庝竴澶╋紝鏄剧ず 鈥渓ast buy鈥?/strong>锛?/p>

select
	sname
	,buydate
    ,lag(buydate,1,'first day') over(partition by sname order by buydate) as 涓婃�¤喘涔版椂闂�
    ,lead(buydate,1,'last day') over(partition by sname order by buydate) as 涓嬫�¤喘涔版椂闂�
from
	cost_fee

缁撴灉濡備笅锛?/p>

鈶?鎴�姝㈠埌褰撳墠鏃ユ湡锛屾瘡浣嶅悓瀛︾殑鈥滈�栨�¤喘涔版椂闂粹�濆拰鈥滄渶鍚庝竴娆¤喘涔版椂闂粹�?/p>

select
	sname
	,buydate
    ,first_value(buydate) over(partition by sname order by buydate) as 棣栨�¤喘涔版椂闂�
    ,last_value(buydate) over(partition by sname order by buydate) as 鏈�鍚庝竴娆¤喘涔版椂闂?
from
	cost_fee

缁撴灉濡備笅锛?/p>

鈶?灞曠ず姣忎綅鍚屽�︾殑鈥滈�栨�¤喘涔版椂闂粹�濆拰鈥滄渶鍚庝竴娆¤喘涔版椂闂粹�?/p>

娉ㄦ剰锛?杩欓噷骞舵病鏈夎�� 鈥滄埅姝㈠埌褰撳墠鏃ユ湡鈥濓紝璇锋敞鎰忊憽鈶�涔嬮棿鐨勫尯鍒�鍛�銆傞渶姹備笉鍚岋紝缁撴灉灏变笉鍚屻�?/p>

select
	sname
	,buydate
    ,first_value(buydate) over(partition by sname order by buydate) as 棣栨�¤喘涔版椂闂�
    ,last_value(buydate) over(partition by sname ) as 鏈�鍚庝竴娆¤喘涔版椂闂?
from
	cost_fee

缁撴灉濡備笅锛?/p>

浠ヤ笂灏辨槸MySQL瀹炴垬绐楀彛鍑芥暟SQL鍒嗘瀽鐝�绾у�︾敓鑰冭瘯鎴愮哗鍙婄敓娲绘秷璐圭殑璇︾粏鍐呭�癸紝鏇村�氬叧浜嶴QL绐楀彛鍑芥暟鍒嗘瀽鎴愮哗鍙婃秷璐圭殑璧勬枡璇峰叧娉ㄨ剼鏈�涔嬪�跺叾瀹冪浉鍏虫枃绔狅紒