sql 统计一次下单率

2015-12-25 01:50:21 711 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL计算复购率需求背景：订单表中有每笔订单的下单时间、用户ID、订单金额等信息，需要统计每个月在接下来几个月用户复购情况。 create table order_info( order_id int primary key, user_id int, amount decimal(10,2), create_time datetime ); insert into order_info values (101,11211,749.00,'2020-01-01 00:04:00'), (102,75205,939.00 阅读详情

select * from she$
where 顾客ID in (select 顾客ID from (select * from she$ where 下单时间 >= '20150717' AND
下单时间 <'20151224' )as ass
group by 顾客ID having count(1)<='1')
and 下单时间 >= '20151223' AND 下单时间 < '20151224'

这样写出的语句和实际的有点差别，顾客ID是唯一标示用户的，每个客户对应的顾客ID 是唯一的。
这个是比较重复率的，但是有些客户第一次下单当天下了2单或者三单就不会被统计的里面。请问这么修改语句能够统计比如说24号在24号之前的重复率为0的是一次下单。

...全文

业务指标分析 | 我用一条SQL统计了PV、UV和二跳率文章目录场景准备业务要求SQL分析场景准备假设有一张track_log表，里面有字段：url、guid、sessionid、ds（url、全局唯一标识、会话id、日期），数据示例如下，其中，一个guid可以有多个sessionid；一个sessionid对应一个guid，但是存在对应多个guid的非法数据；url可能为空。 http://www.yhd.com/?union_ref=7&amp... 阅读详情

萧寒往往

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

萧寒往往 2016-01-07

打赏
举报

elect * into ys from (SELECT  a.[顾客id],a.[当日单量]
,SUM(b.[当日单量]) AS [总单量] ,SUM(b.[当日单量])-a.[当日单量] as 差额
FROM    (
Select [顾客id],[下单时间]=CONVERT(VARCHAR(10),[下单时间],120),COUNT(*) AS 当日单量 from 
she$ WHERE [下单时间]>=@Time AND [下单时间]<@endTime GROUP BY [顾客id],CONVERT(VARCHAR(10),[下单时间],120)
) AS a
INNER JOIN (
Select [顾客id],[下单时间]=CONVERT(VARCHAR(10),[下单时间],120),COUNT(*) AS 当日单量  from 
she$ WHERE [下单时间]>=@Time AND [下单时间]<@endTime GROUP BY [顾客id],CONVERT(VARCHAR(10),[下单时间],120)
) AS b ON a.[顾客id]=b.[顾客id] AND a.[下单时间]>=b.[下单时间]
WHERE   a.[下单时间] >= @startTime
AND a.[下单时间] < @endTime
GROUP BY a.[顾客id],a.[当日单量]) 
as sss

    IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'ttt_final') 
		AND OBJECTPROPERTY(ID, 'IsTable') = 1)
			DROP TABLE ttt_final


		create table ttt_final
		(
			
			客单总金额 decimal(10,4),
			客单量 decimal(10,4) ,
			下单客户数 decimal(10,4),
			客单平均价 decimal(10,4) ,
			总单量 decimal(10,4) ,	
			总金额 decimal(10,4) ,
			客单单量占比 decimal(10,4) ,	
			客单金额占比 decimal(10,4) ,
			优惠券总金额 decimal(10,4) ,
			余额支付 decimal(10,4) ,
			在线支付 decimal(10,4),
			优惠券数量 decimal(10,4) ,
			优惠券金额 decimal(10,4) ,
			优惠券总数量 decimal(10,4) ,

			RowIndex int identity(1,1)

		)
	while(@i<@iTotalTimes) 
	begin
		
		set @i=@i +1

		IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'ttt') 
			AND OBJECTPROPERTY(ID, 'IsTable') = 1)
			   DROP TABLE ttt

		if(@i=@iTotalTimes)
		begin
		

   select * into ttt from she$
 where 顾客ID in (select 顾客id from  ys where
    		   (差额+1<=@iTotalTimes and 差额=0)or (总单量<=@iTotalTimes and 差额>0) )
    		     and 下单时间 >=@startTime and 下单时间<@endTime
		end
		else
		begin
		
		select * into ttt from she$
 where 顾客ID in (select 顾客id from  ys where
    		   (差额+1=@i and 差额=0)or (总单量=@i and 差额>0) )
    		     and 下单时间 >=@startTime and 下单时间<@endTime 
    		       
		end

已经改好了这是部分语句。

Tiger_Zhao 2015-12-28

打赏
举报

[Quote=引用 38 楼 u014051479 的回复:][/Quote]
逻辑没说不清楚，例子也不完整！

假如再来这么几条，顾客21/22算在二次下单里吗？顾客33的二次下单单量算1还是2？
请定义什么叫“二次”！

顾客ID	订单编号	下单时间		

33	2478	2015/8/6 12:59:00

21	2486	2015/8/6 17:56:00

22	2488	2015/8/6 17:56:10

Tiger_Zhao 2015-12-28

打赏
举报

[Quote=引用 29 楼 u014051479 的回复:]比如说有一个客户下单的规律是这样的
订单编号顾客id 详情下单时间
1 5 6666 201512230000
2 5 6666 201512230100
3 5 6666 201512240000
4 5 6666 201512250000
5 5 6666 201512250300
这样统计出来应该是
23号首次下单客户数 1个单量2
24号 3次下单客户数 1 单量 1
25号 5次下单客户数 1 单量 2[/Quote]
你这里统计结果每天有4个逻辑
1)一个客户累计下单数
2)一个客户当日是否为首日下单
3)当日有下单客户数
4)当日下单数
上面的SQL有一个能对才怪呢

清江堤畔 2015-12-28

打赏
举报

其实看了这么多，13、15楼说得对，其实楼主想要做什么呢？谁知道，去重复？这个很简单。问题是楼主想得到的结果是什么，要具体的。是不是统计 24 日增加了多少新客户（以下订单为准）？把问题说清楚才容易得出正确的结果

中国风 2015-12-26

打赏
举报

引用 30 楼 u014051479 的回复:

也就是我之前那样算一次下单率是小于等于实际值的。用原来的统计结果就是 23 号 2次下单客户数0 单量 0 24号 3次下单客户是 1 单量1 25号 5 次下单客户数 1 单量 2

以上是按你的结果集和提供数据拼出来的，你提供的业务数据和结果很逻辑不合乎商业模式，感觉有点怪

中国风 2015-12-26

打赏
举报

#29、#30列的数据是否正确？逻辑上有点怪怪的需求看结果给你拼一下语句，你自己按需求调整

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#she$') is null
	drop table #she$
Go
Create table #she$([订单编号] int,[顾客id] int,[详情] Datetime,[下单时间] Datetime)
Insert #she$
select 1,5,'6666','2015-12-23 00:00' union all
select 2,5,'6666','2015-12-23 01:00' union all
select 3,5,'6666','2015-12-24 00:00' union all
select 4,5,'6666','2015-12-25 00:00' union all
select 5,5,'6666','2015-12-25 03:00'
GO

declare @Time datetime
     set @Time = '20150717'
declare @startTime datetime
     set @startTime = '20151223'
    declare @endTime datetime
    set @endTime = @startTime+3

;WITH Ta
AS
(
Select [顾客id],[下单时间]=CONVERT(VARCHAR(10),[下单时间],120),COUNT(*) AS 购买次数 from #she$ WHERE [下单时间]>=@Time AND [下单时间]<@endTime GROUP BY [顾客id],CONVERT(VARCHAR(10),[下单时间],120)
)
SELECT  a.[顾客id],a.[下单时间],a.[购买次数]
,SUM(b.[购买次数]) AS [总购买次数]
,COUNT(DISTINCT CASE WHEN a.[购买次数]=1 OR a.[下单时间]>b.[下单时间] THEN a.[顾客id] END) AS [客户数]
,COUNT(CASE WHEN a.[下单时间]>b.[下单时间] THEN a.[顾客id] END) AS 单量
FROM    Ta AS a
	INNER JOIN Ta AS b ON a.[顾客id]=b.[顾客id] AND a.[下单时间]>=b.[下单时间]
WHERE   a.[下单时间] >= @startTime
        AND a.[下单时间] < @endTime
GROUP BY a.[顾客id],a.[下单时间],a.[购买次数]

/*
顾客id	下单时间	购买次数	总购买次数	客户数	单量
5	2015-12-23	2	2	0	0
5	2015-12-24	1	3	1	1
5	2015-12-25	2	5	1	2
*/

萧寒往往 2015-12-26

打赏
举报

顾客ID	订单编号	下单时间		
21	2456	2015/8/4 17:56:00 		
21	2458	2015/8/4 17:56:10 		
22	2459	2015/8/4 17:56:20 		
23	2460	2015/8/4 17:56:30 		
45	2465	2015/8/5 13:56:00 		
22	2468	2015/8/5 16:56:00 		
33	2470	2015/8/5 17:56:00 		
9	2474	2015/8/6 1:56:00 		
9	2475	2015/8/6 10:56:00 		
33	2477	2015/8/6 12:56:00 		
				
				
				
	首次下单客户数	首次下单单量	二次下单客户数	二次下单单量
8.4	3	4 	0	0
8.5	2	2 	1	1
8.6	1	2 	1	1

萧寒往往 2015-12-26

打赏
举报

引用 35 楼 roy_88 的回复:

[quote=引用 33 楼 u014051479 的回复:] 一次下单还是只统计了当天只下一单的。下两单的统计不进去。

那应该没有结果集出来 25 号和23号都是2次或以上，不成立 24号--只买一次，但不是新客也不成立这样？ [/quote] 对当天他只买了一次之前要是也买过就不算首次下单，也不统计到一次下单里面，24号之前买了 9单 24号当天只买了一单就算是第十次下单

萧寒往往 2015-12-26

打赏
举报

就是要先判断当天的顾客id 在之前有没有买过东西，如果买过东西就按正常统计了，看重复次数就可以了。重复几次就算几次下单。比如说，这个客户在25号之前买过东西就不统计到一次下单了。他在当天是第几次购买就算第几次下单。如果这个客户之前没有买过东西那么在25号当天记做一次下单 . 无论他在当天下了多少单都统计到一次下单客户里面下单的次数就是单量。

中国风 2015-12-26

打赏
举报

引用 33 楼 u014051479 的回复:

一次下单还是只统计了当天只下一单的。下两单的统计不进去。

那应该没有结果集出来 25 号和23号都是2次或以上，不成立 24号--只买一次，但不是新客也不成立这样？

中国风 2015-12-26

打赏
举报

如：以上数据，你查25号条件购买次数据传多少？查25号购买多次的计算为1次还是多次如：传5时可以查出来 COUNT(*) <= '5' )--总购买次数

萧寒往往 2015-12-26

打赏
举报

一次下单还是只统计了当天只下一单的。下两单的统计不进去。

萧寒往往 2015-12-26

打赏
举报

引用 31 楼 roy_88 的回复:

测测结果对不

        
SELECT  COUNT(DISTINCT 顾客ID ) AS 客户数,COUNT(1) AS 订单数
FROM    she$ AS a
WHERE   EXISTS ( SELECT 1
                 FROM   she$
                 WHERE  顾客ID = a.顾客ID
                        AND 下单时间 >= @Time
                        AND 下单时间 < @endTime
                 HAVING COUNT(*) <= '1' )--总购买次数
        AND 下单时间 >= @startTime
        AND 下单时间 < @endTime

还是不行

中国风 2015-12-25

打赏
举报

测测结果对不

        
SELECT  COUNT(DISTINCT 顾客ID ) AS 客户数,COUNT(1) AS 订单数
FROM    she$ AS a
WHERE   EXISTS ( SELECT 1
                 FROM   she$
                 WHERE  顾客ID = a.顾客ID
                        AND 下单时间 >= @Time
                        AND 下单时间 < @endTime
                 HAVING COUNT(*) <= '1' )--总购买次数
        AND 下单时间 >= @startTime
        AND 下单时间 < @endTime

萧寒往往 2015-12-25

打赏
举报

引用 27 楼 Tiger_Zhao 的回复:

COUNT(DISTINCT 下单时间) 和 count(1) 完全不是一回事啊。你可以说自己改出正确的语句了，千万别说“#18 的算出来的结果是对的啊”。我反复看了#18的代码好多遍，就是没理解怎么结果会是对的

引用 28 楼 roy_88 的回复:

[quote=引用 24 楼 u014051479 的回复:] 但是我用#18 的算出来的结果是对的啊

数据是对的，那可能你的订单下单时间是相同的 [/quote] 也就是我之前那样算一次下单率是小于等于实际值的。用原来的统计结果就是 23 号 2次下单客户数0 单量 0 24号 3次下单客户是 1 单量1 25号 5 次下单客户数 1 单量 2

萧寒往往 2015-12-25

打赏
举报

引用 22 楼 u014051479 的回复:

................

引用 26 楼 u014051479 的回复:

下单时间是 2015-07-01 10:04:00.000 这样的

两位大神！好像算一次的是对了，但是二次三次以后的就不对了，不能去重吧，我最早的语句，是先判断全部的重复率，重复率有一次两次三次等等。先判断重复率然后删选出，重复1次，并且，下单时间是24号的。重复两次下单时间是 24号的等等。然后就汇总出N 次下单率了。。比如说有一个客户下单的规律是这样的订单编号顾客id 详情下单时间 1 5 6666 201512230000 2 5 6666 201512230100 3 5 6666 201512240000 4 5 6666 201512250000 5 5 6666 201512250300 这样统计出来应该是 23号首次下单客户数 1个单量2 24号 3次下单客户数 1 单量 1 25号 5次下单客户数 1 单量 2

中国风 2015-12-25

打赏
举报

引用 24 楼 u014051479 的回复:

但是我用#18 的算出来的结果是对的啊

数据是对的，那可能你的订单下单时间是相同的

Tiger_Zhao 2015-12-25

打赏
举报

COUNT(DISTINCT 下单时间) 和 count(1) 完全不是一回事啊。
你可以说自己改出正确的语句了，千万别说“#18 的算出来的结果是对的啊”。
我反复看了#18的代码好多遍，就是没理解怎么结果会是对的

萧寒往往 2015-12-25

打赏
举报

下单时间是 2015-07-01 10:04:00.000 这样的

萧寒往往 2015-12-25

打赏
举报


  declare @Time datetime
		 set @Time = '20150717'
	declare @startTime datetime
		 set @startTime = '20151223'
        declare @endTime datetime
		set @endTime = @startTime+1
		
select COUNT(*) as '下单客户数' from (select distinct 顾客id from she$ where 下单时间>=@Time  and 下单时间<@endTime)  as  assss

select COUNT(*) as '下单客户数' from (select distinct 顾客id from she$ where 下单时间>=@Time and  下单时间<@startTime) as asss 


select COUNT(*)  from she$ where 顾客id in (select 顾客ID
  from she$
 where 下单时间 >= @startTime -- 取24日记录应该是这样的条件才对！
   AND 下单时间 < @endTime
   AND NOT EXISTS (SELECT *
                     from she$ t
                    where t.顾客ID = she$.顾客ID
                      AND t.下单时间  >= @Time
                      AND t.下单时间 < @startTime )
                     group by 顾客ID having count(1)<='1') 
                     and 下单时间>=@startTime and 下单时间<@endTime
                     
SELECT  DISTINCT 顾客ID  FROM    she$ AS a
WHERE   EXISTS ( SELECT 1
                 FROM   she$
                 WHERE  顾客ID = a.顾客ID
                        AND 下单时间 >= '20150717'
                        AND 下单时间 < '20151223'
                 HAVING COUNT(DISTINCT 下单时间) ='0' )
        AND 下单时间 >= '20151223'
        AND 下单时间 < '20151224'

这是全部的语句得出的结果是 24 号客户总数 52629 23号客户总数52155 用之前的算法算出的一次下单时 466 两个客户总数相差 474 用最后的语句统计的一次下单客户数是 474 单量 483

加载更多回复（24）

sql 统计一次下单率

相关推荐