22,207
社区成员
发帖
与我相关
我的任务
分享
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
已经改好了 这是 部分语句 。顾客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
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
*/
顾客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
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
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