mysql分组后获取若干数据

最近做一个链表分组查询，并且取每个分组的前几位数据的业务

例如有商品表，订单表，品类表，商品表中有自己的分类，并且商品表外键关联品类表主键，订单表外键关联商品表中的商品id

业务需要，根据当前时间区间进行年月周日，进行时间对比，也就是说分组第一优先为时间，其次为商品分类，再到品类，再到商品中的社群属性，一共分组条件为4组

列出关键字段

商品表字段goods_id，goods_type，main_category_id，applicable_crowd，son_category_id等

订单表字段order_id，create_date，order_price等

品类表字段id，category_name等

 

第一解决方案，直接根据上面所有条件进行分组然后在代码层面对数据进行二次筛选，适合小数据量，直接上sql代码

SELECT

                DATE_FORMAT(o.create_date,'%Y-%m-%d') AS create_date,

                o.order_id           AS order_id,

                COUNT(*)             AS order_total,

                g.goods_type         AS goods_type,

                g.main_category_id   AS category_id,

                b.category_name      AS category_name,

                SUM(o.order_price)   AS order_price,

                g.applicable_crowd   AS applicable_crowd,

                g.son_category_id

              FROM `order` o

                JOIN goods_base g

                  ON o.goods_id = g.goods_id

                LEFT JOIN articlecategories b

                  ON g.main_category_id = b.id

              WHERE o.is_delete = 0

                  AND g.is_delete = 0

                  AND DATE_FORMAT(o.create_date, '%Y-%m-%d') >= DATE_FORMAT('2018-05-01', '%Y-%m-%d')

                  AND DATE_FORMAT(o.create_date, '%Y-%m-%d') <= DATE_FORMAT('2018-05-01', '%Y-%m-%d')

              GROUP BY create_date, g.goods_type,g.son_category_id,g.applicable_crowd

              ORDER BY create_date,order_price DESC;

上面的结果集中DATE_FORMAT(o.create_date,'%Y-%m-%d') AS create_date,可以根据条件换成周，月，年

DATE_FORMAT(o.create_date,'%Y-%u') AS create_date周

DATE_FORMAT(o.create_date,'%Y-%m') AS create_date月

DATE_FORMAT(o.create_date,'%Y') AS create_date年

注意：这个时候，分组的条件一定是create_date而不是o.create_date，因为我们是对结果集的create_date进行分组，而不是对原有数据集的o.create_date分组，这个非常重要，同样排序的时候，我们也是对结果集的create_date和结果集中的order_price进行分组的

这种获取到的结果是按时间分组后拿出该时间段所有的数据，需要在业务层代码对其进行一定的分析后或者有用数据

 

 

 

第二种解决方案，需要mysql的虚拟自增id，我们对刚刚的数据集作为一张表，这张表和自己用时间段来进行自联查询获取他们的笛卡尔积，然后通建立一个虚拟自增seq，通过order_price和seq的对比来获取需要笛卡尔积中需要的数据集，本业务中是通过order_price倒序seq升序来排列的，那么条件为a.order_price<=b.order_price and a.seq>=b.seq这个逻辑可以理解成a表中的任意一条数据都能对应到b表中本身的数据和当前价格比他大但是序列号比他小，sql也是利用上面的sql进行自联查询，并赋予一个自增的seq

SELECT

  a.create_date,

  a.order_id,

  a.order_total,

  a.goods_type,

  a.category_id,

  a.category_name,

  a.order_price,

  a.applicable_crowd

FROM (

 

SELECT

          kk.*,

          (@j:=@j+1)    AS seq

        FROM(

 

SELECT 

DATE_FORMAT(o.create_date,'%Y-%m-%d') AS create_date,

o.order_id         AS order_id,

  COUNT(*)           AS order_total,

  g.goods_type       AS goods_type,

  g.main_category_id AS category_id,

  b.category_name    AS category_name,

  SUM(o.order_price) AS order_price,

  g.applicable_crowd AS applicable_crowd,

  g.son_category_id

FROM `order` o JOIN goods_base g 

ON o.goods_id=g.goods_id LEFT JOIN articlecategories b ON g.main_category_id=b.id

WHERE o.is_delete=0 AND g.is_delete=0

AND DATE_FORMAT(o.create_date, '%Y-%m-%d') >= DATE_FORMAT('2018-05-01', '%Y-%m-%d')

AND DATE_FORMAT(o.create_date, '%Y-%m-%d') <= DATE_FORMAT('2018-05-01', '%Y-%m-%d')

GROUP BY create_date,

g.goods_type,g.son_category_id,g.applicable_crowd  ORDER BY create_date,order_price DESC

) kk ,(SELECT @j:=0) it

) a

JOIN 

(

SELECT

          kk.*,

          (@i:=@i+1)    AS seq

        FROM(

 

SELECT 

DATE_FORMAT(o.create_date,'%Y-%m-%d') AS create_date,

o.order_id         AS order_id,

  COUNT(*)           AS order_total,

  g.goods_type       AS goods_type,

  g.main_category_id AS category_id,

  b.category_name    AS category_name,

  SUM(o.order_price) AS order_price,

  g.applicable_crowd AS applicable_crowd,

  g.son_category_id

FROM `order` o JOIN goods_base g 

ON o.goods_id=g.goods_id LEFT JOIN articlecategories b ON g.main_category_id=b.id

WHERE o.is_delete=0 AND g.is_delete=0

AND DATE_FORMAT(o.create_date, '%Y-%m-%d') >= DATE_FORMAT('2018-05-01', '%Y-%m-%d')

AND DATE_FORMAT(o.create_date, '%Y-%m-%d') <= DATE_FORMAT('2018-05-01', '%Y-%m-%d')

GROUP BY create_date,

g.goods_type,g.son_category_id,g.applicable_crowd  ORDER BY create_date,order_price DESC

) kk ,(SELECT @i:=0) it

) b

ON a.create_date = b.create_date

      AND a.order_price <= b.order_price

      AND a.seq >= b.seq

GROUP BY a.create_date,a.goods_type,a.son_category_id,a.applicable_crowd,a.order_price

HAVING COUNT(a.order_price) <= 8

ORDER BY a.create_date,a.order_price DESC;

同样需要注意的是，在第一次分组中create_date和排序中create_date,order_price选择

 

这种方式是一条sql可以解决数据读取问题，并不会返回大量数据，但是在数据量稍微大一点的场景下，sql要反复对其筛选，会非常卡顿

 

 

第三种解决方案，利用代码层面和sql分开做

思路：首先根据业务层传过来的参数，进行判定是否需要拆成多个分组，将分组查询数据的条件准备好，利用CountDownLatch分成子线程来查询