SQL分组查询不会用？手把手教你Group By和聚合函数

知晓 SQL 里存在 Group By 的人数量不少, 然而当真正要去运用它之际, 却老是没法明晰它跟聚合函数究竟是何种关系。实际上, Group By 是用于把数据依据某个字段进行分组的, 而聚合函数乃是在每个分组的内部开展统计计算的工具。这二者必须相互配合使用方可发挥切实的作用并且把其蕴藏的真正的那种强大功效给体现出来。

Group By到底怎么分组

首先来讲讲Group By最为基础的工作形式, 假定你拥有一张销售订单表, 其中包含产品名称、销售数量以及销售日期, 要是你书写SELECT 产品名称 FROM 销售表 GROUP BY 产品名称, 那样的话结果仅仅会展示每个产品名称有一次出现之情状, 这便是分组所达成的效果, 也就是将相同名称的记录集合成单一的组。

不少人会发问, 那其余字段要如何处置呢? 比如说, 我期望浏览每个产品售出的单数详情, 此时聚合函数便发挥作用了。你能够于SELECT之后书写COUNT()来统计每个分组含有的记录条数, 或者借助SUM(销售数量)计算出每个产品的总销售数量。谨记一点: 在GROUP BY查询期间 , SELECT之后要么是分组字段自身 , 要么必定是经聚合函数处理过的字段 , 不可径直书写普通字段。

这里便是实际工作当中最为经常会踩到的坑。举个例子来说, 有人写了这样一段内容, SELECT产品名称, 销售日期, SUM(数量) FROM销售表GROUP BY产品名称, 这样做必然是会报错的, 因为原因在于销售日期它既没有处于分组字段范围之内, 同时它也没有使用聚合函数进行包裹。而正确的操作办法是要把销售日期也添加进GROUP BY当中, 又或者是采用MAX、MIN等函数来进行处理。

聚合函数该怎么配合Group By用

家常必备的聚合函数有五个, 分别是: 用于计数的COUNT, 用于求和的SUM, 用于求平均的AVG, 用于求最大值的MAX, 用于求最小值的MIN。当它们与Group By搭配时, 每个函数会针对每个分组进行独立计算。

举例来讲, 若你期望查看每个月的平均客单价, 那么能够书写SELECT MONTH(下单时间), AVG(金额) FROM 订单表 GROUP BY MONTH(下单时间) 这句语句。在此处, AVG函数将会于每个月份分组范围之内各自去计算得出平均值, 并非是对全表进行一次总的平均计算。

比如说吧, 针对用户活跃度展开分析, 你能够采用SELECT 用户ID, COUNT() FROM 登录日志 GROUP BY 用户ID这种方式, 如此一来, 便会清楚每个用户登录了多少回。要是打算找出登录次数超过100次的用户, 紧接着接上HAVING COUNT() > 100就可以了, 要留意HAVING是专门为聚合之后的条件过滤而设计的, 是不能够使用WHERE来替代的。

有一些场景是需要把多个聚合函数同时运用的, 比如说去查询每一个城市的总的销售额以及最大的单笔订单金额, 此时能够写成SELECT 城市, SUM(金额), MAX(金额) FROM 订单表 GROUP BY 城市 , 通过这样的方式, 一行就能够获取到两个关键的指标, 其效率是非常高的。

https://www.hongxiu.com/booklist/1504771
https://www.hongxiu.com/booklist/1504772
https://www.hongxiu.com/booklist/1504770
https://www.hongxiu.com/booklist/1504769
https://www.hongxiu.com/booklist/1504768
https://www.hongxiu.com/booklist/1504767
https://www.hongxiu.com/booklist/1504766
https://www.hongxiu.com/booklist/1504765
https://www.hongxiu.com/booklist/1504764
https://www.hongxiu.com/booklist/1504763
https://www.hongxiu.com/booklist/1504762
https://www.hongxiu.com/booklist/1504761
https://www.hongxiu.com/booklist/1504760
https://www.hongxiu.com/booklist/1504759
https://www.hongxiu.com/booklist/1504758
https://www.hongxiu.com/booklist/1504757
https://www.hongxiu.com/booklist/1504756
https://www.hongxiu.com/booklist/1504755

https://www.hongxiu.com/booklist/1504754
https://www.hongxiu.com/booklist/1504753
https://www.hongxiu.com/booklist/1504752
https://www.hongxiu.com/booklist/1504751
https://www.hongxiu.com/booklist/1504749
https://www.hongxiu.com/booklist/1504750
https://www.hongxiu.com/booklist/1504748
https://www.hongxiu.com/booklist/1504747
https://www.hongxiu.com/booklist/1504746
https://www.hongxiu.com/booklist/1504745
https://www.hongxiu.com/booklist/1504744
https://www.hongxiu.com/booklist/1504743
https://www.hongxiu.com/booklist/1504742
https://www.hongxiu.com/booklist/1504741
https://www.hongxiu.com/booklist/1504740
https://www.hongxiu.com/booklist/1504739
https://www.hongxiu.com/booklist/1504738
https://www.hongxiu.com/booklist/1504737
https://www.hongxiu.com/booklist/1504736
https://www.hongxiu.com/booklist/1504735
https://www.hongxiu.com/booklist/1504734
https://www.hongxiu.com/booklist/1504733
https://www.hongxiu.com/booklist/1504732
https://www.hongxiu.com/booklist/1504730
https://www.hongxiu.com/booklist/1504731
https://www.hongxiu.com/booklist/1504729
https://www.hongxiu.com/booklist/1504728
https://www.hongxiu.com/booklist/1504727
https://www.hongxiu.com/booklist/1504726
https://www.hongxiu.com/booklist/1504725
https://www.hongxiu.com/booklist/1504724
https://www.hongxiu.com/booklist/1504723
https://www.hongxiu.com/booklist/1504722
https://www.hongxiu.com/booklist/1504721
https://www.hongxiu.com/booklist/1504720
https://www.hongxiu.com/booklist/1504719
https://www.hongxiu.com/booklist/1504718
https://www.hongxiu.com/booklist/1504717
https://www.hongxiu.com/booklist/1504716
https://www.hongxiu.com/booklist/1504715
https://www.hongxiu.com/booklist/1504714
https://www.hongxiu.com/booklist/1504713
https://www.hongxiu.com/booklist/1504712
https://www.hongxiu.com/booklist/1504711
https://www.hongxiu.com/booklist/1504710
https://www.hongxiu.com/booklist/1504709
https://www.hongxiu.com/booklist/1504708
https://www.hongxiu.com/booklist/1504707
https://www.hongxiu.com/booklist/1504706
https://www.hongxiu.com/booklist/1504705
https://www.hongxiu.com/booklist/1504704
https://www.hongxiu.com/booklist/1504703
https://www.hongxiu.com/booklist/1504702
https://www.hongxiu.com/booklist/1504701
https://www.hongxiu.com/booklist/1504700
https://www.hongxiu.com/booklist/1504699
https://www.hongxiu.com/booklist/1504698
https://www.hongxiu.com/booklist/1504697
https://www.hongxiu.com/booklist/1504696
https://www.hongxiu.com/booklist/1504695
https://www.hongxiu.com/booklist/1504694
https://www.hongxiu.com/booklist/1504693
https://www.hongxiu.com/booklist/1504692
https://www.hongxiu.com/booklist/1504691
https://www.hongxiu.com/booklist/1504690
https://www.hongxiu.com/booklist/1504689
https://www.hongxiu.com/booklist/1504688
https://www.hongxiu.com/booklist/1504687
https://www.hongxiu.com/booklist/1504686
https://www.hongxiu.com/booklist/1504685
https://www.hongxiu.com/booklist/1504684
https://www.hongxiu.com/booklist/1504683
https://www.hongxiu.com/booklist/1504682
https://www.hongxiu.com/booklist/1504681
https://www.hongxiu.com/booklist/1504680
https://www.hongxiu.com/booklist/1504679
https://www.hongxiu.com/booklist/1504678
https://www.hongxiu.com/booklist/1504677
https://www.hongxiu.com/booklist/1504676
https://www.hongxiu.com/booklist/1504675
https://www.hongxiu.com/booklist/1504674
https://www.hongxiu.com/booklist/1504673
https://www.hongxiu.com/booklist/1504672
https://www.hongxiu.com/booklist/1504671
https://www.hongxiu.com/booklist/1504670
https://www.hongxiu.com/booklist/1504669
https://www.hongxiu.com/booklist/1504668
https://www.hongxiu.com/booklist/1504667
https://www.hongxiu.com/booklist/1504666
https://www.hongxiu.com/booklist/1504665
https://www.hongxiu.com/booklist/1504664
https://www.hongxiu.com/booklist/1504663
https://www.hongxiu.com/booklist/1504662
https://www.hongxiu.com/booklist/1504661
https://www.hongxiu.com/booklist/1504660
https://www.hongxiu.com/booklist/1504659
https://www.hongxiu.com/booklist/1504658
https://www.hongxiu.com/booklist/1504657
https://www.hongxiu.com/booklist/1504656
https://www.hongxiu.com/booklist/1504655
https://www.hongxiu.com/booklist/1504654
https://www.hongxiu.com/booklist/1504653
https://www.hongxiu.com/booklist/1504652
https://www.hongxiu.com/booklist/1504651
https://www.hongxiu.com/booklist/1504650
https://www.hongxiu.com/booklist/1504649
https://www.hongxiu.com/booklist/1504648
https://www.hongxiu.com/booklist/1504647
https://www.hongxiu.com/booklist/1504646
https://www.hongxiu.com/booklist/1504645
https://www.hongxiu.com/booklist/1504644
https://www.hongxiu.com/booklist/1504643
https://www.hongxiu.com/booklist/1504642
https://www.hongxiu.com/booklist/1504641
https://www.hongxiu.com/booklist/1504640
https://www.hongxiu.com/booklist/1504639
https://www.hongxiu.com/booklist/1504638
https://www.hongxiu.com/booklist/1504637
https://www.hongxiu.com/booklist/1504636
https://www.hongxiu.com/booklist/1504635
https://www.hongxiu.com/booklist/1504634
https://www.hongxiu.com/booklist/1504633
https://www.hongxiu.com/booklist/1504632
https://www.hongxiu.com/booklist/1504631
https://www.hongxiu.com/booklist/1504630
https://www.hongxiu.com/booklist/1504629
https://www.hongxiu.com/booklist/1504628
https://www.hongxiu.com/booklist/1504627
https://www.hongxiu.com/booklist/1504626
https://www.hongxiu.com/booklist/1504625
https://www.hongxiu.com/booklist/1504624
https://www.hongxiu.com/booklist/1504623
https://www.hongxiu.com/booklist/1504622
https://www.hongxiu.com/booklist/1504621
https://www.hongxiu.com/booklist/1504620
https://www.hongxiu.com/booklist/1504619
https://www.hongxiu.com/booklist/1504618
https://www.hongxiu.com/booklist/1504617
https://www.hongxiu.com/booklist/1504616
https://www.hongxiu.com/booklist/1504615
https://www.hongxiu.com/booklist/1504614
https://www.hongxiu.com/booklist/1504613
https://www.hongxiu.com/booklist/1504612
https://www.hongxiu.com/booklist/1504611
https://www.hongxiu.com/booklist/1504610
https://www.hongxiu.com/booklist/1504609
https://www.hongxiu.com/booklist/1504608
https://www.hongxiu.com/booklist/1504607
https://www.hongxiu.com/booklist/1504606
https://www.hongxiu.com/booklist/1504605
https://www.hongxiu.com/booklist/1504604
https://www.hongxiu.com/booklist/1504603
https://www.hongxiu.com/booklist/1504602
https://www.hongxiu.com/booklist/1504601
https://www.hongxiu.com/booklist/1504600
https://www.hongxiu.com/booklist/1504599
https://www.hongxiu.com/booklist/1504598
https://www.hongxiu.com/booklist/1504597
https://www.hongxiu.com/booklist/1504596
https://www.hongxiu.com/booklist/1504595

https://www.hongxiu.com/booklist/1504809
https://www.hongxiu.com/booklist/1504808
https://www.hongxiu.com/booklist/1504807
https://www.hongxiu.com/booklist/1504806
https://www.hongxiu.com/booklist/1504805
https://www.hongxiu.com/booklist/1504804
https://www.hongxiu.com/booklist/1504803
https://www.hongxiu.com/booklist/1504802
https://www.hongxiu.com/booklist/1504801
https://www.hongxiu.com/booklist/1504800
https://www.hongxiu.com/booklist/1504799
https://www.hongxiu.com/booklist/1504798
https://www.hongxiu.com/booklist/1504797
https://www.hongxiu.com/booklist/1504796
https://www.hongxiu.com/booklist/1504795
https://www.hongxiu.com/booklist/1504794
https://www.hongxiu.com/booklist/1504793
https://www.hongxiu.com/booklist/1504792
https://www.hongxiu.com/booklist/1504791
https://www.hongxiu.com/booklist/1504790
https://www.hongxiu.com/booklist/1504789
https://www.hongxiu.com/booklist/1504788
https://www.hongxiu.com/booklist/1504787
https://www.hongxiu.com/booklist/1504786
https://www.hongxiu.com/booklist/1504785
https://www.hongxiu.com/booklist/1504784
https://www.hongxiu.com/booklist/1504783
https://www.hongxiu.com/booklist/1504781
https://www.hongxiu.com/booklist/1504782
https://www.hongxiu.com/booklist/1504780
https://www.hongxiu.com/booklist/1504779
https://www.hongxiu.com/booklist/1504778
https://www.hongxiu.com/booklist/1504777
https://www.hongxiu.com/booklist/1504776
https://www.hongxiu.com/booklist/1504775
https://www.hongxiu.com/booklist/1504774
https://www.hongxiu.com/booklist/1504773