select * from
(select 客户ID,点击消费,date from datasource where date>getdate()-32)a
pivot(max(点击消费) for date in([2012-9-12],[2012-9-13]))b
left join
(select 客户ID,avg(hittime) as hittime from downaccount
where date >=getdate()-32
group by 客户ID)c on b.客户ID=c.客户ID
left join
(select 客户ID,case when sum(点击次数)=0 then 0 else sum(点击消费)/sum(点击次数) end as acp
from datasource st where st.date >=getdate()-32
group by 客户ID)d on b.客户ID=d.客户ID
(select 客户ID,点击消费,date from datasource where date>getdate()-32)a
pivot(max(点击消费) for date in([2012-9-12],[2012-9-13]))b
left join
(select 客户ID,avg(hittime) as hittime from downaccount
where date >=getdate()-32
group by 客户ID)c on b.客户ID=c.客户ID
left join
(select 客户ID,case when sum(点击次数)=0 then 0 else sum(点击消费)/sum(点击次数) end as acp
from datasource st where st.date >=getdate()-32
group by 客户ID)d on b.客户ID=d.客户ID
date 这个列有索引吗?hittime 有没有非聚集索引?
数据量多大?
这个sql可以优化吗?
比如:
declare @date datetime = getdate()-32...
select 客户ID,点击消费,date from datasource where date>@date
...2. 最后的d表中,case when sum(点击次数)=0省去,直接计算sum(点击消费)/sum(点击次数),最后where中限制sum(点击次数)>0, 如果sum(点击次数)=0,最后取值的时候再计算。3. select * from
(select 客户ID,点击消费,date from datasource where date>getdate()-32)a
pivot(max(点击消费) for date in([2012-9-12],[2012-9-13]))b
这一段使用with应该能提高效率最后代码大致如下:declare @date datetime = getdate()-32with tb as
(
select *, ISNULL(d.acp, 0) from
(select 客户ID,点击消费,date from datasource where date>@date)a
pivot(max(点击消费) for date in([2012-9-12],[2012-9-13]))b
)
select * from tb
left join
(select 客户ID,avg(hittime) as hittime from downaccount
where date >=@date
group by 客户ID)c on b.客户ID=c.客户ID
left join
(select 客户ID,sum(点击消费)/sum(点击次数) as acp
from datasource st where st.date >=@date and sum(点击次数)>0
group by 客户ID)d on b.客户ID=d.客户ID
我在看看PIVOT
SELECT *
FROM (
SELECT 客户ID , 点击消费 , date FROM datasource WHERE date> GETDATE ()- 32
) a PIVOT ( MAX(点击消费) FOR date IN ([2012-9-12] , [2012-9-13]) ) b
LEFT JOIN (
SELECT 客户ID , AVG(hittime) AS hittime,CASE WHEN SUM(点击次数) = 0 THEN 0
ELSE SUM(点击消费)/SUM(点击次数)
END AS acp
FROM downaccount
WHERE date >= GETDATE()-32
GROUP BY 客户ID
) c
ON b.客户ID = c.客户ID
4. 最后select的时候,别用select *,而是把你需要的列写出来。
你看下
SELECT *
FROM (
SELECT 客户ID , 点击消费 , date FROM datasource WHERE date> GETDATE ()- 32
) a PIVOT ( MAX(点击消费) FOR date IN ([2012-9-12] , [2012-9-13]) ) b和
SELECT 客户ID , AVG(hittime) AS hittime,CASE WHEN SUM(点击次数) = 0 THEN 0
ELSE SUM(点击消费)/SUM(点击次数)
END AS acp
FROM downaccount
WHERE date >= GETDATE()-32
GROUP BY 客户ID分别用多久把PIVOT 改成case when 测一下又是什么情况