倒是都在一张订单表里cust_order 全部新客户。
order_id user_id Created_Dttm
109980 34 2009-9-9
109981 35 2008-12-3
109993 36 2009-8-7
113021 431 2008-12-31
这张表里一共3580行 也就说有3580个userID要查的是从日期2008-12-1到2009-10-30这段时间内1周后 客户第二次下订单2-4周
4周后 我目前想到的:
即 1周后 user_id 是否再次出现
1 逐个查 但是显然不现实 3580个 估计得写一天。
2 select user_id from [cust_order] where ([Created_Dttm] between '2008-12-1' and '2009-10-30') and user_id in (select user_id from [cust_order] where [Created_Dttm] between '2008-12-1' and '2009-12-8')
这样查下去,也很繁琐。所以请大家出个好主意吧。谢谢大虾们。
英文是这样描述的:Total number of new customers
Percentage of them placed the 2nd order in 1 week after the 1st order
Percentage of them placed the 2nd order b/t 2-4 weeks after the 1st order
order_id user_id Created_Dttm
109980 34 2009-9-9
109981 35 2008-12-3
109993 36 2009-8-7
113021 431 2008-12-31
这张表里一共3580行 也就说有3580个userID要查的是从日期2008-12-1到2009-10-30这段时间内1周后 客户第二次下订单2-4周
4周后 我目前想到的:
即 1周后 user_id 是否再次出现
1 逐个查 但是显然不现实 3580个 估计得写一天。
2 select user_id from [cust_order] where ([Created_Dttm] between '2008-12-1' and '2009-10-30') and user_id in (select user_id from [cust_order] where [Created_Dttm] between '2008-12-1' and '2009-12-8')
这样查下去,也很繁琐。所以请大家出个好主意吧。谢谢大虾们。
英文是这样描述的:Total number of new customers
Percentage of them placed the 2nd order in 1 week after the 1st order
Percentage of them placed the 2nd order b/t 2-4 weeks after the 1st order
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-13 11:51:07
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int,[id] int,[date] datetime)
insert [tb]
select 109980,34,'2009-9-9' union all
select 109981,35,'2008-12-3' union all
select 109993,36,'2009-8-7' union all
select 113021,431,'2008-12-31' union all
select 115355,34,'2009-9-19' union all
select 115895,36,'2009-9-1'
--------------开始查询--------------------------
select id0=row_number()over(partition by id order by getdate()),* from [tb]
----------------结果----------------------------
/* id0 col id date
-------------------- ----------- ----------- -----------------------
1 109980 34 2009-09-09 00:00:00.000
2 115355 34 2009-09-19 00:00:00.000
1 109981 35 2008-12-03 00:00:00.000
1 109993 36 2009-08-07 00:00:00.000
2 115895 36 2009-09-01 00:00:00.000
1 113021 431 2008-12-31 00:00:00.000(6 行受影响)
*/
FROM TB A
JOIN (
SELECT ID, MIN(DATE) AS DATE
FROM TB
GROUP BY ID) B
ON A.DATE <> B.DATE AND A.ID = B.ID
你是每个USERID上次下单日为起,一周后?
恩 对 你真聪明。这是销售要的统计表 可把我愁死了。是每个user 一周 两周到四周
他们主要想看看客户的忠诚度。
go
create table [tb]([col] int,[id] int,[date] datetime)
insert [tb]
select 109980,34,'2009-9-9' union all
select 109981,35,'2008-12-3' union all
select 109993,36,'2009-8-7' union all
select 113021,431,'2008-12-31' union all
select 115355,34,'2009-9-19' union all
select 115895,36,'2009-9-1'select * from tb
select id,datepart(week,date) week into #tb from tb
group by datepart(week,date),id--一周后
select id from #tb b
where exists(select 1 from #tb where #tb.id=b.id and b.week-week=1)
--两周后
select id from #tb b
where exists(select 1 from #tb where #tb.id=b.id and b.week-week=2)
很菜的方法,看看行不。
Total number of new customers
Percentage of them placed the 2nd order in 1 week after the 1st order
Percentage of them placed the 2nd order b/t 2-4 weeks after the 1st order
declare @tmpTable table(oid int,uid int,odate datetime) --订单号,user_id ,下单时间
declare @tmpUid table(uid int,weeks int) --user_id, 该用户第二单间隔周数(无第二单weeks=-1)
declare @oid int
declare @uid int
declare @odate datetime
declare @odate2 datetimeinsert into @tmpTable select order_id,[user_id],Created_Dttm from view_3 order by Created_Dttm ascwhile exists (select * from @tmpTable)
begin
select @oid=oid,@uid=uid,@odate=odate from @tmpTable
if exists (select * from @tmpTable where oid<>@oid and uid=@uid)
begin
select @odate2 = min(odate) from @tmpTable where oid<>@oid and uid=@uid
select @weeks = datediff(week,@odate,@odate2)
end
else
begin
select @weeks=-1
end
delete @tmpTable where uid=@uid
insert into @tmpUid (uid,weeks) values (@uid,@weeks)
end
select * from @tmpUid where weeks=-1
select * from @tmpUid where weeks between 0 and 1
select * from @tmpUid where weeks between 2 and 3
select * from @tmpUid where weeks>=4我最终的答案 但是执行起来效率太低 有没有快点的方法呢
weeks还有等于这些的
-40
-33
-42
-41
-4
-15
go
create table [tb]([col] int,[id] int,[date] datetime)
INSERT INTO [tb]
select 109980,34,'2009-9-9' union all
select 109981,35,'2008-12-3' union all
select 109993,36,'2009-8-7' union all
select 113021,431,'2008-12-31' union all
select 115355,34,'2009-9-19'SELECT * FROM tb a WHERE EXISTS(SELECT 1 FROM tb b WHERE DATEDIFF(DAY,a.date,b.date)<=7 AND DATEDIFF(DAY,a.date,b.date)>=0 AND a.col<>b.col AND a.id=b.id)SELECT * FROM tb a WHERE EXISTS(SELECT 1 FROM tb b WHERE DATEDIFF(DAY,a.date,b.date)>14 AND DATEDIFF(DAY,a.date,b.date)<=28 AND DATEDIFF(DAY,a.date,b.date)>=0 AND a.col<>b.col AND a.id=b.id)是这个意思么
go
create table [tb]([col] int,[uid] int,[date] datetime)
INSERT INTO [tb]
select 109980,34,'2009-9-9' union all
select 109981,35,'2008-12-3' union all
select 109993,36,'2009-8-7' union all
select 113021,431,'2008-12-31' union all
select 115355,34,'2009-9-19' union ALL
select 115356,36,'2009-8-19'
--1周内第二次
SELECT uid FROM tb a WHERE EXISTS(SELECT 1 FROM tb b WHERE DATEDIFF(DAY,a.date,b.date)<=7 AND DATEDIFF(DAY,a.date,b.date)>=0 AND a.col<>b.col AND a.uid=b.uid) GROUP BY uid
--2到4周内第二次
SELECT uid FROM tb a WHERE EXISTS(SELECT 1 FROM tb b WHERE DATEDIFF(DAY,a.date,b.date)>7 AND DATEDIFF(DAY,a.date,b.date)<=28 AND DATEDIFF(DAY,a.date,b.date)>=0 AND a.col<>b.col AND a.uid=b.uid) GROUP BY uid--结果
uid
34
36刚才发错了 是这样吗
2没下过单的人
3只下过一次的人
4所有下过2单或2以上的人所有的订单
5然后查出第一单第二单的时间
6第一单:select min(Created_Dttm) as T2,[User_id] from 表 group by User_id
第二单:select user_id,min(created_dttm) as T1 from 表 where created_dttm not in
(select min(created_dttm) from 表 group by user_id)group by user_id order by user_id