查询6种产品组合被多少客户购买过,速度太慢,帮我看下

amazeyeli 2014-04-28 11:02:17
CREATE TABLE TEST
(
CUSTOMER INT NOT NULL,
RECNO INT NOT NULL,
PRODUCT VARCHAR(20)
);
GO
INSERT INTO TEST VALUES
----数据在这里http://pan.baidu.com/s/1mg0qgBm
GO
----查询6种产品组合被多少客户购买过
;WITH T AS
(
SELECT DISTINCT PRODUCT FROM TEST
),
A AS
(
SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),
T1.PRODUCT AS PRO1,T2.PRODUCT AS PRO2,T3.PRODUCT AS PRO3,T4.PRODUCT AS PRO4,T5.PRODUCT AS PRO5,T6.PRODUCT AS PRO6
FROM T AS T1
JOIN T AS T2
ON T1.PRODUCT < T2.PRODUCT
JOIN T AS T3
ON T2.PRODUCT < T3.PRODUCT
JOIN T AS T4
ON T3.PRODUCT < T4.PRODUCT
JOIN T AS T5
ON T4.PRODUCT < T5.PRODUCT
JOIN T AS T6
ON T5.PRODUCT < T6.PRODUCT
),
B AS
(
SELECT ID,PRODUCT FROM A
UNPIVOT(PRODUCT FOR PRO IN(PRO1,PRO2,PRO3,PRO4,PRO5,PRO6))P
),
C AS
(
SELECT TEST.CUSTOMER,B.ID,B.PRODUCT
FROM TEST
JOIN B
ON TEST.PRODUCT = B.PRODUCT
GROUP BY TEST.CUSTOMER,B.ID,B.PRODUCT
HAVING COUNT(TEST.RECNO)=6
),
D AS
(
SELECT ID,PRODUCT,COUNT(CUSTOMER) AS CT
FROM C
GROUP BY ID,PRODUCT
)
SELECT TOP 10 * FROM D ORDER BY CT DESC
...全文
197 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
amazeyeli 2014-04-29
  • 打赏
  • 举报
回复
成攻了,6分钟 15322 bourbon 24 15322 cracker 24 15322 heineken 24 15322 hering 24 15322 olives 24 15322 soda 24 11498 bourbon 22 11498 chicken 22 11498 coke 22 11498 heineken 22 top10应该改成top60
amazeyeli 2014-04-29
  • 打赏
  • 举报
回复
引用 1 楼 ap0405140 的回复:
CTE语句中A表的数据量应该不小喔?其上又没有索引,因此性能不佳. 建议把CTE语句拆分为若干个查询语句,分别存为临时表,并建相应的索引.
;WITH T AS 
(
    SELECT DISTINCT PRODUCT FROM TEST
),
    A AS 
(
    SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),
        T1.PRODUCT AS PRO1,T2.PRODUCT AS PRO2,T3.PRODUCT AS PRO3,T4.PRODUCT AS PRO4,T5.PRODUCT AS PRO5,T6.PRODUCT AS PRO6
    FROM T AS T1
    JOIN T AS T2
    ON T1.PRODUCT < T2.PRODUCT 
    JOIN T AS T3
    ON T2.PRODUCT < T3.PRODUCT 
    JOIN T AS T4
    ON T3.PRODUCT < T4.PRODUCT 
    JOIN T AS T5
    ON T4.PRODUCT < T5.PRODUCT 
    JOIN T AS T6
    ON T5.PRODUCT < T6.PRODUCT
),
    B AS
(
    SELECT ID,PRODUCT FROM A
    UNPIVOT(PRODUCT FOR PRO IN(PRO1,PRO2,PRO3,PRO4,PRO5,PRO6))P
)
SELECT * INTO C FROM B
CREATE INDEX INDEX_C ON C(PRODUCT)
CREATE INDEX INDEX_T ON TEST(PRODUCT)
;WITH D AS
(
    SELECT TEST.CUSTOMER,C.ID
    FROM TEST 
    JOIN C
    ON TEST.PRODUCT = C.PRODUCT 
    GROUP BY TEST.CUSTOMER,C.ID
    HAVING COUNT(TEST.RECNO)=6  
),
    E AS
(
    SELECT ID,COUNT(CUSTOMER) AS CT
    FROM D
    GROUP BY ID
)
SELECT TOP 10 C.*,E.CT FROM C JOIN E ON C.ID=E.ID ORDER BY CT DESC 
应该创建哪种索引?
唐诗三百首 2014-04-28
  • 打赏
  • 举报
回复
CTE语句中A表的数据量应该不小喔?其上又没有索引,因此性能不佳.
建议把CTE语句拆分为若干个查询语句,分别存为临时表,并建相应的索引.

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧