技术分析

jsfx
数据可视化技术分析

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

技术分析3383年前 (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

分享给朋友:

相关文章

如何利用ElasticSearch群集搭建,实现高可用

如何利用ElasticSearch群集搭建,实现高可用

ElasticSearch简介:ElasticSearch 是一个分布式、高扩展、高实时的搜索与数据分析引擎。它能很方便的使大量数据具有搜索、分析和探索的能力。充分利用ElasticSearch的水平...

为什么不再使用D3.js、Echarts做数据可视化

为什么不再使用D3.js、Echarts做数据可视化

D3是什么全称是(Data-Driven Documents),一个被数据驱动的文档简单点,是一个JavaScript函数库,使用它主要用来做数据可视化。D3 是一个开源项目,作者是纽约时报的工程师。...

SpringBoot+SparkSQL操作JSON字符串

SpringBoot+SparkSQL操作JSON字符串

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

利用org.mybatis.generator生成实体类

利用org.mybatis.generator生成实体类

springboot+maven+mybatis+mysql 利用org.mybatis.generator生成实体类1.添加pom依赖: 2.编写generatorConfig....

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

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

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

将.NET Core编写的数据可视化项目打包成Docker镜像,实现容器化部署

将.NET Core编写的数据可视化项目打包成Docker镜像,实现容器化部署

第一步 环境准备,安装docker环境:1234567891011通过命令安装docker:sudo apt-get install -y docker.io 启动Docker服务...

发表评论

访客

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

体验账号:administrator 密码:administrator

  • 7X12小时
    7X12小时

    专家1V1服务

  • 业务保障
    业务保障

    扎实数据根基

  • 合作伙伴
    合作伙伴

    NBIDataVis

  • 强大交付
    强大交付

    实现客户价值