美文网首页大数据可视化分析随笔-生活工作点滴
分享SQL,查询用户最近一次购买时间间隔

分享SQL,查询用户最近一次购买时间间隔

作者: NBI大数据可视化分析 | 来源:发表于2019-07-09 13:23 被阅读23次

    (1)先创建一张测试表:

      CREATE TABLE `用户购买订单` (

      `购买时间` datetime(6) NULL DEFAULT NULL,

      `用户` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

      `费用` decimal(20, 2) NULL DEFAULT NULL

      ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

    (2)往数据表填充数据:

    INSERT INTO `用户购买订单` VALUES ('2019-04-16 00:00:00.000000', '张三', 100.00);

      INSERT INTO `用户购买订单` VALUES ('2019-04-15 00:00:00.000000', '李四', 95.00);

      INSERT INTO `用户购买订单` VALUES ('2019-04-14 00:00:00.000000', '张A', 85.00);

      INSERT INTO `用户购买订单` VALUES ('2019-04-13 00:00:00.000000', '张B', 70.00);

      INSERT INTO `用户购买订单` VALUES ('2019-04-12 00:00:00.000000', '张C', 77.00);

      INSERT INTO `用户购买订单` VALUES ('2019-04-11 00:00:00.000000', '张D', 68.00);

      INSERT INTO `用户购买订单` VALUES ('2019-04-10 00:00:00.000000', '张E', 53.00);

      INSERT INTO `用户购买订单` VALUES ('2019-05-16 00:00:00.000000', '张三', 100.00);

      INSERT INTO `用户购买订单` VALUES ('2019-06-15 00:00:00.000000', '李四', 95.00);

      INSERT INTO `用户购买订单` VALUES ('2019-03-14 00:00:00.000000', '张A', 85.00);

      INSERT INTO `用户购买订单` VALUES ('2019-09-13 00:00:00.000000', '张B', 70.00);

      INSERT INTO `用户购买订单` VALUES ('2019-10-12 00:00:00.000000', '张C', 77.00);

      INSERT INTO `用户购买订单` VALUES ('2019-08-11 00:00:00.000000', '张D', 68.00);

      INSERT INTO `用户购买订单` VALUES ('2019-09-10 00:00:00.000000', '张E', 53.00);

      INSERT INTO `用户购买订单` VALUES ('2018-02-05 00:00:00.000000', '张三', 100.00);

    (3)实现思路:

      (3.1)首先做个合并表查询:

          SELECT

            用户购买订单.购买时间,用户购买订单.用户,a.购买时间 '购买时间1',a.用户 '用户1'

          FROM

            用户购买订单

          INNER JOIN

            (SELECT * from 用户购买订单)a

          ON

            用户购买订单.`用户` = a.`用户` AND 用户购买订单.购买时间 <> a.购买时间

    查询结果:

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    (3.2)基于3.1求时间差:

        SELECT

          购买时间,用户,购买时间1,用户1,DATEDIFF(购买时间,购买时间1) as '购买日期间隔'

        FROM

          (SELECT

            用户购买订单.购买时间,用户购买订单.用户,a.购买时间 '购买时间1',a.用户 '用户1'

          FROM

            用户购买订单

          INNER JOIN

            (SELECT * from 用户购买订单)a

          ON

            用户购买订单.`用户` = a.`用户` AND 用户购买订单.购买时间 <> a.购买时间

          )b

    查询结果:

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    (3.3)过滤掉"购买日期间隔"为负数的行:

        SELECT

           购买时间,用户,购买时间1,用户1,DATEDIFF(购买时间,购买时间1) as '购买日期间隔'

        FROM

            (SELECT

              用户购买订单.购买时间,用户购买订单.用户,a.购买时间 '购买时间1',a.用户 '用户1'

             FROM

              用户购买订单

            INNER JOIN

              (SELECT * from 用户购买订单)a

            ON

              用户购买订单.`用户` = a.`用户` AND 用户购买订单.购买时间 <> a.购买时间

            )b

        WHERE

            DATEDIFF(购买时间,购买时间1)>=0

    查询结果:

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    (3.4)因为我们要求每个用户最近一次与上一次购买的时间间隔,所以,我们要基于上图对用户分组,去购买日期间隔最小值:

          SELECT

              用户,MIN(购买日期间隔) '距离上一次购买天数'

          FROM

            (

            SELECT

                购买时间,用户,购买时间1,用户1,DATEDIFF(购买时间,购买时间1) as '购买日期间隔'

            FROM

              (SELECT

                用户购买订单.购买时间,用户购买订单.用户,a.购买时间 '购买时间1',a.用户 '用户1'

              FROM

                用户购买订单

              INNER JOIN

                (SELECT * from 用户购买订单)a

              ON

                用户购买订单.`用户` = a.`用户` AND 用户购买订单.购买时间 <> a.购买时间

                )b

            WHERE

              DATEDIFF(购买时间,购买时间1)>=0

          )c

          GROUP BY

               用户

    查询结果:

    相关文章

      网友评论

        本文标题:分享SQL,查询用户最近一次购买时间间隔

        本文链接:https://www.haomeiwen.com/subject/iilxkctx.html