技术分析

jsfx
数据可视化技术分析

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

技术分析7945年前 (2019-07-09)大数据

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

复制代码
1   CREATE TABLE `用户购买订单` (2 3   `购买时间` datetime(6) NULL DEFAULT NULL,4 5   `用户` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,6 7   `费用` decimal(20, 2) NULL DEFAULT NULL8 9   ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
复制代码

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

复制代码
 1   INSERT INTO `用户购买订单` VALUES ('2019-04-16 00:00:00.000000', '张三', 100.00); 2  3   INSERT INTO `用户购买订单` VALUES ('2019-04-15 00:00:00.000000', '李四', 95.00); 4  5   INSERT INTO `用户购买订单` VALUES ('2019-04-14 00:00:00.000000', '张A', 85.00); 6  7   INSERT INTO `用户购买订单` VALUES ('2019-04-13 00:00:00.000000', '张B', 70.00); 8  9   INSERT INTO `用户购买订单` VALUES ('2019-04-12 00:00:00.000000', '张C', 77.00);10 11   INSERT INTO `用户购买订单` VALUES ('2019-04-11 00:00:00.000000', '张D', 68.00);12 13   INSERT INTO `用户购买订单` VALUES ('2019-04-10 00:00:00.000000', '张E', 53.00);14 15   INSERT INTO `用户购买订单` VALUES ('2019-05-16 00:00:00.000000', '张三', 100.00);16 17   INSERT INTO `用户购买订单` VALUES ('2019-06-15 00:00:00.000000', '李四', 95.00);18 19   INSERT INTO `用户购买订单` VALUES ('2019-03-14 00:00:00.000000', '张A', 85.00);20 21   INSERT INTO `用户购买订单` VALUES ('2019-09-13 00:00:00.000000', '张B', 70.00);22 23   INSERT INTO `用户购买订单` VALUES ('2019-10-12 00:00:00.000000', '张C', 77.00);24 25   INSERT INTO `用户购买订单` VALUES ('2019-08-11 00:00:00.000000', '张D', 68.00);26 27   INSERT INTO `用户购买订单` VALUES ('2019-09-10 00:00:00.000000', '张E', 53.00);28 29   INSERT INTO `用户购买订单` VALUES ('2018-02-05 00:00:00.000000', '张三', 100.00);
复制代码

 

(3)实现思路:

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

复制代码
 1       SELECT 2  3         用户购买订单.购买时间,用户购买订单.用户,a.购买时间 '购买时间1',a.用户 '用户1' 4  5       FROM 6  7         用户购买订单 8  9       INNER JOIN10 11         (SELECT * from 用户购买订单)a12 13       ON14 15         用户购买订单.`用户` = a.`用户` AND 用户购买订单.购买时间 <> a.购买时间
复制代码

 

查询结果:

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

(3.2)基于3.1求时间差:

复制代码
 1     SELECT 2  3       购买时间,用户,购买时间1,用户1,DATEDIFF(购买时间,购买时间1) as '购买日期间隔' 4  5     FROM 6  7       (SELECT 8  9         用户购买订单.购买时间,用户购买订单.用户,a.购买时间 '购买时间1',a.用户 '用户1'10 11       FROM12 13         用户购买订单14 15       INNER JOIN16 17         (SELECT * from 用户购买订单)a18 19       ON20 21         用户购买订单.`用户` = a.`用户` AND 用户购买订单.购买时间 <> a.购买时间22 23       )b
复制代码

 

查询结果:

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

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

复制代码
 1     SELECT 2  3        购买时间,用户,购买时间1,用户1,DATEDIFF(购买时间,购买时间1) as '购买日期间隔' 4  5     FROM 6  7         (SELECT 8  9           用户购买订单.购买时间,用户购买订单.用户,a.购买时间 '购买时间1',a.用户 '用户1'10 11          FROM12 13           用户购买订单14 15         INNER JOIN16 17           (SELECT * from 用户购买订单)a18 19         ON20 21           用户购买订单.`用户` = a.`用户` AND 用户购买订单.购买时间 <> a.购买时间22 23         )b24 25     WHERE26 27         DATEDIFF(购买时间,购买时间1)>=0
复制代码

 

查询结果:

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

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

复制代码
 1       SELECT 2  3           用户,MIN(购买日期间隔) '距离上一次购买天数' 4  5       FROM 6  7         ( 8  9         SELECT10 11             购买时间,用户,购买时间1,用户1,DATEDIFF(购买时间,购买时间1) as '购买日期间隔'12 13         FROM14 15           (SELECT16 17             用户购买订单.购买时间,用户购买订单.用户,a.购买时间 '购买时间1',a.用户 '用户1'18 19           FROM20 21             用户购买订单22 23           INNER JOIN24 25             (SELECT * from 用户购买订单)a26 27           ON28 29             用户购买订单.`用户` = a.`用户` AND 用户购买订单.购买时间 <> a.购买时间30 31             )b32 33         WHERE34 35           DATEDIFF(购买时间,购买时间1)>=036 37       )c38 39       GROUP BY40 41            用户
复制代码

 

查询结果:


扫描二维码推送至手机访问。

版权声明:本文由策意data发布,如需转载请注明出处。

转载请注明出处http://dsj.ceyicm.cn/reed/11.html

分享给朋友:

相关文章

从dockerhub拉取NBI可视化产品镜像

从dockerhub拉取NBI可视化产品镜像

前两篇文章介绍了将docker镜像打包,镜像发布,那么今天为大家介绍完一个闭环操作,如何从dockerhua拉取镜像,运行镜像,下面将演示从ubuntu和centos系统上拉取和运行镜像操作:操作流程...

实战:SpringBoot+KafKa实现生产者和消费者功能

实战:SpringBoot+KafKa实现生产者和消费者功能

1.配置pom包<dependency>  <groupId>org.springframework.kafka</groupId>  <...

.NET Core中过滤器Filter的使用介绍

.NET Core中过滤器Filter的使用介绍

知识点回顾:前面几篇文章分别给大家介绍了(1)Swagger的集成和用法;(2)JWT身份验证的集成和用法;(3)OOM框架AnutoMapper对象映射的用法;今天给大家介绍过滤器Filter的基本...

.NET Core WebAPI集成Swagger做接口管理

.NET Core WebAPI集成Swagger做接口管理

什么是Swagger?Swagger 是一个规范且完整的框架,用于生成、描述、调用和可视化 RESTful 风格的 Web 服务。Swagger 的目标是对 REST API 定义一个标准且和语言无关...

SpringBoot+SparkSQL操作JSON字符串

SpringBoot+SparkSQL操作JSON字符串

在SpringBoot中通过maven来做包管理构建,有几个地方需要注意一下的,需要解决包之间的冲突,否则运行时会报错:(1)sparkSQL中需要先排除两个包: 1  ...

.NET Core WebAPI集成JWT,实现身份验证

.NET Core WebAPI集成JWT,实现身份验证

前两篇文章给大家介绍了在.NET Core中如何使用Swagger的文章,那今天给大家分享一下JWT在做接口开发的同学可能都有感受,我的接口如何保护的问题,如果没有身份验证,那不是接口完全暴露在外面,...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。
每一次合作都多一位朋友

体验账号:administrator 密码:administrator

  • 7X12小时
    7X12小时

    专家1V1服务

  • 业务保障
    业务保障

    扎实数据根基

  • 合作伙伴
    合作伙伴

    NBIDataVis

  • 强大交付
    强大交付

    实现客户价值