知晓 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/1504771https://www.hongxiu.com/booklist/1504772https://www.hongxiu.com/booklist/1504770https://www.hongxiu.com/booklist/1504769https://www.hongxiu.com/booklist/1504768https://www.hongxiu.com/booklist/1504767https://www.hongxiu.com/booklist/1504766https://www.hongxiu.com/booklist/1504765https://www.hongxiu.com/booklist/1504764https://www.hongxiu.com/booklist/1504763https://www.hongxiu.com/booklist/1504762https://www.hongxiu.com/booklist/1504761https://www.hongxiu.com/booklist/1504760https://www.hongxiu.com/booklist/1504759https://www.hongxiu.com/booklist/1504758https://www.hongxiu.com/booklist/1504757https://www.hongxiu.com/booklist/1504756https://www.hongxiu.com/booklist/1504755https://www.hongxiu.com/booklist/1504754https://www.hongxiu.com/booklist/1504753https://www.hongxiu.com/booklist/1504752https://www.hongxiu.com/booklist/1504751https://www.hongxiu.com/booklist/1504749https://www.hongxiu.com/booklist/1504750https://www.hongxiu.com/booklist/1504748https://www.hongxiu.com/booklist/1504747https://www.hongxiu.com/booklist/1504746https://www.hongxiu.com/booklist/1504745https://www.hongxiu.com/booklist/1504744https://www.hongxiu.com/booklist/1504743https://www.hongxiu.com/booklist/1504742https://www.hongxiu.com/booklist/1504741https://www.hongxiu.com/booklist/1504740https://www.hongxiu.com/booklist/1504739https://www.hongxiu.com/booklist/1504738https://www.hongxiu.com/booklist/1504737https://www.hongxiu.com/booklist/1504736https://www.hongxiu.com/booklist/1504735https://www.hongxiu.com/booklist/1504734https://www.hongxiu.com/booklist/1504733https://www.hongxiu.com/booklist/1504732https://www.hongxiu.com/booklist/1504730https://www.hongxiu.com/booklist/1504731https://www.hongxiu.com/booklist/1504729https://www.hongxiu.com/booklist/1504728https://www.hongxiu.com/booklist/1504727https://www.hongxiu.com/booklist/1504726https://www.hongxiu.com/booklist/1504725https://www.hongxiu.com/booklist/1504724https://www.hongxiu.com/booklist/1504723https://www.hongxiu.com/booklist/1504722https://www.hongxiu.com/booklist/1504721https://www.hongxiu.com/booklist/1504720https://www.hongxiu.com/booklist/1504719https://www.hongxiu.com/booklist/1504718https://www.hongxiu.com/booklist/1504717https://www.hongxiu.com/booklist/1504716https://www.hongxiu.com/booklist/1504715https://www.hongxiu.com/booklist/1504714https://www.hongxiu.com/booklist/1504713https://www.hongxiu.com/booklist/1504712https://www.hongxiu.com/booklist/1504711https://www.hongxiu.com/booklist/1504710https://www.hongxiu.com/booklist/1504709https://www.hongxiu.com/booklist/1504708https://www.hongxiu.com/booklist/1504707https://www.hongxiu.com/booklist/1504706https://www.hongxiu.com/booklist/1504705https://www.hongxiu.com/booklist/1504704https://www.hongxiu.com/booklist/1504703https://www.hongxiu.com/booklist/1504702https://www.hongxiu.com/booklist/1504701https://www.hongxiu.com/booklist/1504700https://www.hongxiu.com/booklist/1504699https://www.hongxiu.com/booklist/1504698https://www.hongxiu.com/booklist/1504697https://www.hongxiu.com/booklist/1504696https://www.hongxiu.com/booklist/1504695https://www.hongxiu.com/booklist/1504694https://www.hongxiu.com/booklist/1504693https://www.hongxiu.com/booklist/1504692https://www.hongxiu.com/booklist/1504691https://www.hongxiu.com/booklist/1504690https://www.hongxiu.com/booklist/1504689https://www.hongxiu.com/booklist/1504688https://www.hongxiu.com/booklist/1504687https://www.hongxiu.com/booklist/1504686https://www.hongxiu.com/booklist/1504685https://www.hongxiu.com/booklist/1504684https://www.hongxiu.com/booklist/1504683https://www.hongxiu.com/booklist/1504682https://www.hongxiu.com/booklist/1504681https://www.hongxiu.com/booklist/1504680https://www.hongxiu.com/booklist/1504679https://www.hongxiu.com/booklist/1504678https://www.hongxiu.com/booklist/1504677https://www.hongxiu.com/booklist/1504676https://www.hongxiu.com/booklist/1504675https://www.hongxiu.com/booklist/1504674https://www.hongxiu.com/booklist/1504673https://www.hongxiu.com/booklist/1504672https://www.hongxiu.com/booklist/1504671https://www.hongxiu.com/booklist/1504670https://www.hongxiu.com/booklist/1504669https://www.hongxiu.com/booklist/1504668https://www.hongxiu.com/booklist/1504667https://www.hongxiu.com/booklist/1504666https://www.hongxiu.com/booklist/1504665https://www.hongxiu.com/booklist/1504664https://www.hongxiu.com/booklist/1504663https://www.hongxiu.com/booklist/1504662https://www.hongxiu.com/booklist/1504661https://www.hongxiu.com/booklist/1504660https://www.hongxiu.com/booklist/1504659https://www.hongxiu.com/booklist/1504658https://www.hongxiu.com/booklist/1504657https://www.hongxiu.com/booklist/1504656https://www.hongxiu.com/booklist/1504655https://www.hongxiu.com/booklist/1504654https://www.hongxiu.com/booklist/1504653https://www.hongxiu.com/booklist/1504652https://www.hongxiu.com/booklist/1504651https://www.hongxiu.com/booklist/1504650https://www.hongxiu.com/booklist/1504649https://www.hongxiu.com/booklist/1504648https://www.hongxiu.com/booklist/1504647https://www.hongxiu.com/booklist/1504646https://www.hongxiu.com/booklist/1504645https://www.hongxiu.com/booklist/1504644https://www.hongxiu.com/booklist/1504643https://www.hongxiu.com/booklist/1504642https://www.hongxiu.com/booklist/1504641https://www.hongxiu.com/booklist/1504640https://www.hongxiu.com/booklist/1504639https://www.hongxiu.com/booklist/1504638https://www.hongxiu.com/booklist/1504637https://www.hongxiu.com/booklist/1504636https://www.hongxiu.com/booklist/1504635https://www.hongxiu.com/booklist/1504634https://www.hongxiu.com/booklist/1504633https://www.hongxiu.com/booklist/1504632https://www.hongxiu.com/booklist/1504631https://www.hongxiu.com/booklist/1504630https://www.hongxiu.com/booklist/1504629https://www.hongxiu.com/booklist/1504628https://www.hongxiu.com/booklist/1504627https://www.hongxiu.com/booklist/1504626https://www.hongxiu.com/booklist/1504625https://www.hongxiu.com/booklist/1504624https://www.hongxiu.com/booklist/1504623https://www.hongxiu.com/booklist/1504622https://www.hongxiu.com/booklist/1504621https://www.hongxiu.com/booklist/1504620https://www.hongxiu.com/booklist/1504619https://www.hongxiu.com/booklist/1504618https://www.hongxiu.com/booklist/1504617https://www.hongxiu.com/booklist/1504616https://www.hongxiu.com/booklist/1504615https://www.hongxiu.com/booklist/1504614https://www.hongxiu.com/booklist/1504613https://www.hongxiu.com/booklist/1504612https://www.hongxiu.com/booklist/1504611https://www.hongxiu.com/booklist/1504610https://www.hongxiu.com/booklist/1504609https://www.hongxiu.com/booklist/1504608https://www.hongxiu.com/booklist/1504607https://www.hongxiu.com/booklist/1504606https://www.hongxiu.com/booklist/1504605https://www.hongxiu.com/booklist/1504604https://www.hongxiu.com/booklist/1504603https://www.hongxiu.com/booklist/1504602https://www.hongxiu.com/booklist/1504601https://www.hongxiu.com/booklist/1504600https://www.hongxiu.com/booklist/1504599https://www.hongxiu.com/booklist/1504598https://www.hongxiu.com/booklist/1504597https://www.hongxiu.com/booklist/1504596https://www.hongxiu.com/booklist/1504595https://www.hongxiu.com/booklist/1504809https://www.hongxiu.com/booklist/1504808https://www.hongxiu.com/booklist/1504807https://www.hongxiu.com/booklist/1504806https://www.hongxiu.com/booklist/1504805https://www.hongxiu.com/booklist/1504804https://www.hongxiu.com/booklist/1504803https://www.hongxiu.com/booklist/1504802https://www.hongxiu.com/booklist/1504801https://www.hongxiu.com/booklist/1504800https://www.hongxiu.com/booklist/1504799https://www.hongxiu.com/booklist/1504798https://www.hongxiu.com/booklist/1504797https://www.hongxiu.com/booklist/1504796https://www.hongxiu.com/booklist/1504795https://www.hongxiu.com/booklist/1504794https://www.hongxiu.com/booklist/1504793https://www.hongxiu.com/booklist/1504792https://www.hongxiu.com/booklist/1504791https://www.hongxiu.com/booklist/1504790https://www.hongxiu.com/booklist/1504789https://www.hongxiu.com/booklist/1504788https://www.hongxiu.com/booklist/1504787https://www.hongxiu.com/booklist/1504786https://www.hongxiu.com/booklist/1504785https://www.hongxiu.com/booklist/1504784https://www.hongxiu.com/booklist/1504783https://www.hongxiu.com/booklist/1504781https://www.hongxiu.com/booklist/1504782https://www.hongxiu.com/booklist/1504780https://www.hongxiu.com/booklist/1504779https://www.hongxiu.com/booklist/1504778https://www.hongxiu.com/booklist/1504777https://www.hongxiu.com/booklist/1504776https://www.hongxiu.com/booklist/1504775https://www.hongxiu.com/booklist/1504774https://www.hongxiu.com/booklist/1504773