sql环比

A. 如何用sql分析电商用户行为数据（案例）

        

本文以“淘宝用户行为数据集”的分析全过程为例，展示数据分析的全过程

——使用工具：MySQL，Excel，Navicat，PowerBI

——分析类型：描述分析，诊断分析

——分析方法：漏斗分析，用户路径分析，RFM用户价值分析，活跃/存留分析，帕累托分析，假设验证分析。

（考虑到阅读体验文章中只放了SQL截图，如需PDF版本，再公众号后台回复“用户行为分析”领取）

（目录如下）

       

1.分析流程和方法

当没有清晰的数据看板时我们需要先清洗杂乱的数据，基于分析模型做可视化，搭建描述性的数据看板。

然后基于描述性的数据挖掘问题，提出假设做优化，或者基于用户特征数据进行预测分析找规律，基于规律设计策略。简单来说：

——描述性分析就是：“画地图”

——诊断性分析就是：“找问题”

——预测性分析就是 ：“找规律”

在数据分析中有两个典型的场景：

一种是有数据，没有问题：需要先整体分析数据，然后再根据初步的描述分析，挖掘问题做诊断性分析，提出假设，设计策略解决问题。

 

另一种是已经发现了问题，或者已经有了假设，这种做数据分析更偏向于验证假设。

 

2.淘宝用户行为分析

本次是对“淘宝用户行为数据集”进行分析，在分析之前我们并不知道有什么问题，所以需要先进行描述性分析，分析数据挖掘问题。

我们首先来看下这个数据集的元数据：

       

根据以上数据字段我们可以拿用户行为为主轴从纵深方向提出一些问题，然后再从数据中找答案

       

纵向：

——这个数据集中用户的日活跃和周活跃时间有什么规律吗？

——在当日活跃的用户次日，三日，四日……还有多少活跃？

深向：

——用户从浏览到购买的整体转化率怎么样？

——用户从浏览到购买的路径是怎么样子的？ 

——平台主要会给用户推送什么商品？

——用户喜欢什么类目？喜欢什么商品？ 

——怎么判断哪些是高价值用户 ？ 

 

 

下面是叮当整理的常用分析方法：      

我们可以给前面的问题匹配一下分析方法，便于后面的分析：

为了便于后面的数据分析，在分析之前我们需要先对做一下清洗

看元数据（字段解释，数据来源，数据类型，数据量……）初步发现问题为之后的处理做准备。

       

确定缺失值范围，去除不需要字段，填充缺失内容    

根据元数据格式和后续分析需要的格式对数据进行处理

  

去除重复值，异常值

——去除重复值：并把用户ID,商品ID，时间戳设置为主键

——异常值处理：查询并删除2017年11月25日至2017年12月3日之外的数据

     

查询并删除小于2017-11-25的

——验证数据：      

——分析思路：

——SQL提数：

       

       

——Excel可视化：

       

活跃曲线整体为上升状态，同为周六日，12月2号，3号相比11月25日，26日活跃度更高。

用户在周六周日相比其他时间更活跃（周六周日为休息日，用户有更多时间）

      

一天内用户活跃的最高峰期为21点（用户在这个时间段空闲较多）

 

——分析思路：

——SQL提数：

列出每用户每天及当天后面又活跃的日期，并创建“活跃时间间隔表”用于后面求次日存留，三日存留……

       

对“活跃时间间隔表视图”引用进行分组统计，计算每日存留人数并创建视图

对存留人数表进行计算，统计活跃用户留存率

——Excel可视化：

       

——分析思路：

——SQL提数：

-把各种用户行为分离出来并创建视图方便后续查询用户行为数据

查询整体数据漏斗

——Excel可视化：

       

用户从浏览到购买整体转化率2.3%，具体主要在哪个环节流失还需要再细分用户路径分析

 

——分析思路：

       

——SQL提数：

——PowerBI可视化：

       

用户从浏览到购买的路径主要有4条，路径越长转化率越底

路径1：浏览→购买：转化率1.45%

路径2：浏览→加购物车→购买：转化率0.33

路径3：浏览→收藏→购买：转化率0.11%

路径4：浏览→收藏→加购物车→购买：转化率0.03%

——分析思路：

——SQL提数：

——Excel可视化：

       

——描述性分析：

浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越大在这个阶梯中的商品越少，越靠后商品浏览量阶梯之间的落差相对越小，同阶梯内的商品越多。

浏览量TOP100的商品所属类目中，4756105，3607361，4357323三个类目浏览量远超其他类目。

——分析思路：

——SQL提数：

查询计算商品转化率，升序排列，取前100个

       

——Excel可视化：

       

——描述性分析：

从商品看：有17款商品转化率超过了1。

从类目看：这些商品所属类目分布均匀，除965809，4801426，2735466，2640118，5063620，4789432，2945933这7个类目之外，其他类目都只有一个商品在转化率TOP100的商品中。

——分析思路：

用户价值分析常用的分析方式是RFM模型

       

本次分析中的R,F,M具体定义（仅用于演示分析方法，无实际业务参考价值）：

 

——SQL取数与分析：

1）建立打分标准：先计算R,F的值，并排序，根据R,F值最大值和最小值得区间设计本次得打分标准

-查询并计算R，F值创建视图

       

-引用RF数值表，分别查询R,F的最大值和最小值

       

       

-结合人工浏览的建立打分标准      

2）给R，F按价值打分

3）计算价值的平均值

       

4）用平均值和用户分类规则表比较得出用户分类   

     

——Excel可视化      

 

通过描述性分析得到可视化的数据后我们一般会先看一下是否符合业务常识

如果符合常识接下来我们会通过与行业平均数据和本产品的同比环比对比看是否正常，如果不正常就要找原因，设计解决方案，如果正常那就看是否有可以优化的地方。

       

我们首先来看一下这些描述性分析是否符合业务常识和指标是否正常：

       

1.活跃曲线整体为上升状态，同为周六日，12月2号，3号相比11月25日，26日活跃度更高。

2.用户在周六周日相比其他时间更活跃

3.一天内用户活跃的最高峰期为21点

4.从2017年11月15日致2017年12月3日，活跃用户新增38%

5.从2017年11月15日致2017年12月3日，活跃用户次日留存增长18.67%，当日的活跃用户留存也在快速增长，第七日留存比次日留存高18.56%。

6.用户从浏览到购买整体转化率2.3%

7.用户从浏览到购买的路径主要有4条，路径越长转化率越低。

8.浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越大在这个阶梯中的商品越少，越靠后商品浏览量阶梯之间的落差相对越小，同阶梯内的商品越多。

9.浏览量TOP100的商品所属类目中，4756105，3607361，4357323三个类目浏览量远超其他类目。

10.从商品看：有17款商品转化率超过了1。

11.从类目看：这些商品所属类目分布均匀，除965809，4801426，2735466，2640118，5063620，4789432，2945933这7个类目之外，其他类目都只有一个商品在转化率TOP100的商品中。

根据以上诊断分析我们梳理出了以下假设，做假设验证。

       

 

假设1：这些商品中有高转化率的爆款商品

       

 

对比浏览量TOP5的商品，发现这些商品转化率在同一类目下并不高，假设不成立

 

假设2：4756105，3607361，4357323三个类目属于高频刚需类目

-创建类目购买频次表

       

-计算类目购买频次平均值

       

-查询4756105，3607361，4357323三个类目的购买频次       

4756105，3607361，4357323三个类目的用户购买频次明显高于平均值，假设成立

 

假设3：有部分用户是未点击商详直接从收藏和购物车购买的。

       

用户不是直接从收藏和购物车购买的，只是后续复购未点击商详，假设不成立

 

假设4：淘宝推荐的商品主要是“同一类目下的高转化商品”

       

用Excel对浏览量TOP100的商品ID和转化率TOP100的商品ID进行去重，结果无重复值，假设不成立

3.结论：

1）用户活跃：用户活跃曲线整体呈上升趋势，在一周中周六，周日活跃度比平时更高，在一天中用户活跃曲线从凌晨4点开始往上升，在中午12点和下午5~6点有两个小低谷（吃饭），到晚上9点时活跃度达到顶峰。

 

2）用户留存：从2017年11月15日致2017年12月3日的用户留存数据来看，淘宝的用户留存数据较好，活跃用户次日留存增长18.67%，当日的活跃用户留存也在快速增长，第七日留存比次日留存高18.56%。

 

3）用户转化：整体转化2.3%，用户从浏览到购买的路径主要有4条，路径越长转化率越低。

4）平台推荐与用户偏好：从数据集中的数据来看，排除用户兴趣偏好标签，淘宝给用户用户推送的商品主要是高频刚需的类目，促使用户复购，流量回流平台。

 

以上结论受数据量和数据类型的影响，并不一定准确，仅用来练习数据分析方法。

（考虑到阅读体验文章中只放了SQL截图，如需PDF版本，再公众号后台回复“用户行为分析”领取）

B. sql字段设计中如何实现同比环比功能

select '上期' as '期间' ,sum(sl) sl,sum(je) je from subfhd where kdrq between '2016-06-01' and '2016-06-30'union select '本期' as '期间',sum(sl) sl,sum(je) je from subfhd where kdrq between '2016-07-01' and '2016-07-31'

C. 这是一个环比增长率的SQL但是运行结果却有问题，求大神解决一下

可能是你在分区里漏了销售员了，光是按年度肯定是不够的，加上销售员然后把lag单独拿出来做一列看一下确保正确，再作计算。

D. qlikview 怎么做环比

qlikview需要用到sql计算，可以用excel直接添加公式进去就好啦，或者直接使用DataFocus ，中文搜索输入“按xx计算的yy的X增长率”就可得到环比啦~

E. SQL里怎么查询销售同比与环比

与上月比即为环比，与上年同月比即为同比
select sum(case when to_char(fdbs,'yyyy')-1 || to_char(fdbs,'MM‘) =年月参数 then sshje else 0 end ) 上年同期,sum(case when to_char(fdbs,'yyyy') || to_char(fdbs,'MM‘)-1 = 年月参数 then sshje else 0 end ) 上月销售额,sum(case when to_char(fdbs,'yyyy') || to_char(fdbs,'MM') = 年月参数 then sshje else 0 end ) 本月销售额
from retmls

F. sqlServer 实现环比

实施鼎捷软件易飞ERP，轻松实现企业商业智能化管理!

实施的易飞ERP系统，包括了基本信息模块、采购管理、存货管理、销售管理、BOM、工单及委外管理、批次需求计划、应收应付管理、会计总帐管理、自动分录管理、成本管理共11个模块。

易飞ERP是由制造、质量、财务、销售和人力资源等30多个模块组成，其功能覆盖了企业管理的方方面面。易飞ERP强调系统集成性，并可根据各企业的不同需求加以调整组合。易飞ERP集易操作性、高技术水准和强大功能于一身。经过15000家客户的经验积累，沉淀了厚重的制造、流通行业知识。主要功能有：信息实时掌握 全球经营监控；内控内审循环；制造管理；进销存管理；财务管理；商业智能；人力资源管理；智能的管理应用工具等。
另外,虚机团上产品团购,超级便宜

G. 如何用mysql实现环比的计算

SELECT
t.bill_fee,
y.bill_fee,
CASE

WHEN y.bill_fee IS NULL
OR y.bill_fee = 0 THEN
0.00 ELSE round(( t.bill_fee - y.bill_fee )/ y.bill_fee * 100, 2 )
END ratio
FROM
( SELECT DATE_FORMAT( t.attr_cycle, '%Y%m%d' ) AS attr_cycle, T.BILL_FEE FROM dd_result t ) t
LEFT JOIN (
SELECT
DATE_FORMAT( t.attr_cycle, '%Y%m%d' ) AS attr_cycle,
DATE_FORMAT( DATE_ADD( t.attr_cycle, INTERVAL 1 DAY ), '%Y%m%d' ) AS tomorrow,
T.BILL_FEE
FROM
dd_result t
) y ON t.attr_cycle = y.tomorrow

H. 请教Oracle计算同比和环比sql语句

首先明确概念：

环比增长率＝（本期数－上期数）/上期数*100％反映本期比上期增长了多少。

同比增长率＝（本期数－同期数）/同期数*100％指和去年同期相比较的增长率。

数据表名：d_temp_data
查询数据如下：

查询的sql语句如下：

selecta.*,

nvl(round(money/lag(money)over(orderbyid)*100,2),'0')||'%'"同比",

nvl(round(money/lag(money)over(partitionbymonorderbyid)*100,2),'0')||'%'"环比"

fromd_temp_dataa

idinameyearmonmoney同比环比

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

1 1 飞马股 2013 01 300 0% 0%

2 2 飞马股 2013 02 270 90% 0%

3 3 飞马股 2013 03 350 129.63% 0%

4 4 飞马股 2013 04 180 51.43% 0%

5 5 飞马股 2013 05 500 277.78% 0%

6 6 飞马股 2013 06 400 80% 0%

7 7 飞马股 2014 01 210 52.5% 70%

8 8 飞马股 2014 02 240 114.29% 88.89%

9 9 飞马股 2014 03 320 133.33% 91.43%

10 10 飞马股 2014 04 480 150% 266.67%

11 11 飞马股 2014 05 400 83.33% 80%

I. 求问Mysql同比环比SQL语句怎么写

首先明确概念：
环比增长率＝（本期数－上期数）/上期数*100％ 反映本期比上期增长了多少。
同比增长率＝（本期数－同期数）/同期数*100％ 指和去年同期相比较的增长率。

J. sql问题，关于sql时间上处理上的问题

环境sqlserver 2008下

declare @user0 as long --当期
declare @user1 as long --当期1
declare @user2 as long --当期2

--为天时

select @user0 = count(*) from 会员表 where 日期 = @ap_data
select @user1 = count(*) from 会员表 where 日期 = dateadd(day,-1,=@ap_data)
select @user2 = count(*) from 会员表 where 日期 = dateadd(day,-2,=@ap_data)

--为周

select @user0 = count(*) from 会员表 where 日期 <= @ap_data and 日期 >=dateadd(day,-7,=@ap_data)
select @user1 = count(*) from 会员表 where 日期 <= dateadd(day,-8,=@ap_data) and 日期 >=dateadd(day,-14,=@ap_data)
select @user2 = count(*) from 会员表 where 日期 <= dateadd(day,-15,=@ap_data) and 日期 >=dateadd(day,-21,=@ap_data)

--为月时，修改以上几个数字，不粘贴了