May 20, 2012 at 10:49 pm
Yes, I did.
Plz see key1=002 and 003 with rank_within_key_product.
key1dateproduct_idrank_within_key_and_productrank_within_keycounting
00120100403004111
00120100505003122
00120100615002133
00120100716001144
00120110813001254
00120110923001364
00220100403002111
00220100612002221
00220110503004133 (should be 2)
00220120103005144 (should be 3)
00320100612002111
00320100831005122
00320110214005232
00320110603005342
00320120803001155 (should be 3)
May 20, 2012 at 11:49 pm
CREATE TABLE table2
(
key1 char(3),
[date] date,
product_id char(3),
value1 integer,
);
INSERT INTO dbo.table2 VALUES ('001','20100505','003',300);
INSERT INTO dbo.table2 VALUES ('001','20100615','002',200);
INSERT INTO dbo.table2 VALUES ('001','20100716','001',100);
INSERT INTO dbo.table2 VALUES ('001','20110813','001',100);
INSERT INTO dbo.table2 VALUES ('001','20110923','001',100);
INSERT INTO dbo.table2 VALUES ('001','20100403','004',400);
INSERT INTO dbo.table2 VALUES ('002','20100612','002',400);
INSERT INTO dbo.table2 VALUES ('002','20110503','004',500);
INSERT INTO dbo.table2 VALUES ('002','20120103','005',200);
INSERT INTO dbo.table2 VALUES ('002','20100403','002',200);
INSERT INTO dbo.table2 VALUES ('003','20100612','002',300);
INSERT INTO dbo.table2 VALUES ('003','20100831','005',500);
INSERT INTO dbo.table2 VALUES ('003','20110214','005',500);
INSERT INTO dbo.table2 VALUES ('003','20110603','005',500);
INSERT INTO dbo.table2 VALUES ('003','20120803','001',100);
CREATE UNIQUE INDEX uq1 ON dbo.table2 (key1, [date]) INCLUDE (product_id);
-- SQL Server 2012 only
SELECT
key1,
product_id,
product_sw =
1 + COUNT_BIG(changed) OVER (
PARTITION BY key1
ORDER BY [date])
FROM
(
SELECT
t.key1,
t.product_id,
t.[date],
CASE
WHEN product_id !=
LAG(product_id) OVER (
PARTITION BY key1
ORDER BY [date])
THEN 1
END AS changed
FROM dbo.table2 AS t
) AS sq1
ORDER BY
key1,
[date];
-- SQL Server 2005+
SELECT
t.key1,
t.product_id,
t.[date],
changed = ISNULL(prev.changed, 1)
INTO #t
FROM table2 AS t
OUTER APPLY
(
SELECT TOP (1)
changed = CASE WHEN t2.product_id = t.product_id THEN 0 ELSE 1 END
FROM table2 AS t2
WHERE
t2.key1 = t.key1
AND t2.[date] < t.[date]
ORDER BY
t2.[date] DESC
) AS prev;
CREATE UNIQUE CLUSTERED INDEX c ON #t (key1, [date]);
SELECT
t.key1,
t.product_id,
t.[date],
product_sw =
(
SELECT
SUM(t2.changed)
FROM #t AS t2
WHERE
t2.key1 = t.key1
AND t2.[date] <= t.[date]
)
FROM #t AS t
ORDER BY
t.key1,
t.[date];
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2012 at 12:32 am
I think it worked for him...I asked him to post the performance matrxi for his table.
select t2.*,dtb.cnt from
table2 t2
inner join (
select *,row_number() over(partition by key1 order by date) as cnt
from (
select key1, date, product_id, row_number() over (partition by key1 ,product_id order by date) as rank_within_key_and_product
from table2
) dta where rank_within_key_and_product = 1
) dtb
on t2.key1= dtb.key1
and t2.product_id = dtb.product_id
order by key1, date
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 12:48 am
Gullimeel (5/21/2012)
I think it worked for him...I asked him to post the performance matrxi for his table.
Ah, I wish it had been clearer that the partitions were ordered by date.
Of course you'll only get the right sorting if the column is changed to a date type rather than the varchar(16) as given :rolleyes:
-- Updated solution for SQL 2012 only
SELECT
key1,
product_id,
product_sw =
1 + COUNT_BIG(changed) OVER (
PARTITION BY key1
ORDER BY [date])
FROM
(
SELECT
t.key1,
t.product_id,
t.[date],
CASE
WHEN product_id !=
LAG(product_id) OVER (
PARTITION BY key1
ORDER BY [date])
THEN 1
END AS changed
FROM dbo.table2 AS t
) AS sq1
ORDER BY
key1,
[date];
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 22, 2012 at 3:01 am
Hi, Gullimeel and SQL Kiwi,
Thank you very much! The SQLs posted work perfect.
There are 20TB DBs in total to make switching matrix with SQL2008R2 for Markov chain analysis with OR methods.
I cannot send performance data because security policy at my company. Sorry.
Anyway, I really appreciate your work!
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply