June 15, 2009 at 8:15 pm
Lets say I have a "date" column (PK one row per day) and a "item" column (1 of 10 different items will be in this column)
I'd like to count the average amount of rows between an exact match for all of the items.
For example:
Date: Item:
2009-06-15 2
2009-06-14 1
2009-06-13 6
2009-06-12 2
2009-06-11 1
2009-06-10 6
2009-06-09 2
2009-06-08 1
in the simple above example you see that the average rows between an exact match is 2
Id like to know the best way to go about creating a query to do this and keeping everything ordered by date desc.
Any ideas?
Thanks
June 15, 2009 at 10:53 pm
select avg(Item) from TableName
June 15, 2009 at 11:27 pm
Hi there,
I think you should try this and tell me if I have understood your problem right. I would appreciate a response
create table #tblItem( dt datetime, ItemID int)
insert into #tblItem
select getDate()-1,2 union all
select getDate()-2,1 union all
select getDate()-3,6 union all
select getDate()-4,2 union all
select getDate()-5,1 union all
select getDate()-6,6 union all
select getDate()-7,3 union all
select getDate()-8,4 ;
with AccItem as
(
select top 100 percent rank() over (order by dt) as SrNo,dt,ItemID
from #tblItem order by dt
)
select avg(a2.SrNo-a1.SrNo-1) as Average from AccItem a1 join AccItem a2
on a1.ItemID=a2.ItemID and a2.SrNo>a1.SrNo
drop table #tblItem
please change sample data according to your plan
Thanks for posting
June 16, 2009 at 6:31 am
I think you want the average number of rows between consecutive rows with the same itemId with the row order defined by the date column, rather than the average number of rows between all pairs of rows with the same itemId. If so then the following should work.
Note that I haven't tested this query yet.
CREATE TABLE #tblItem (dt datetime, itemId int)
DECLARE @Today datetime
SELECT @Today = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
INSERT INTO #tblItem (dt, itemId)
SELECT @Today - 1, 2 UNION ALL
SELECT @Today - 2, 1 UNION ALL
SELECT @Today - 3, 6 UNION ALL
SELECT @Today - 4, 2 UNION ALL
SELECT @Today - 5, 1 UNION ALL
SELECT @Today - 6, 6 UNION ALL
SELECT @Today - 7, 2 UNION ALL
SELECT @Today - 8, 1
;WITH AccItem AS (
SELECT itemId,
ROW_NUMBER() OVER (ORDER BY dt) AS rnAll,
ROW_NUMBER() OVER (PARTITION BY itemId ORDER BY dt) AS rnItem
FROM #tblItem
)
SELECT
A.itemId,
AVG(B.rnAll - A.rnAll - 1) AS AvgRowsBetween
FROM AccItem A
INNER JOIN AccItem B ON (A.itemId = B.itemId AND A.rnItem + 1 = B.rnItem)
GROUP BY A.itemId
ORDER BY A.itemId
EDIT:
Or if you want the overall average for all values of itemId...
;WITH AccItem AS (
SELECT itemId,
ROW_NUMBER() OVER (ORDER BY dt) AS rnAll,
ROW_NUMBER() OVER (PARTITION BY itemId ORDER BY dt) AS rnItem
FROM #tblItem
)
SELECT
AVG(B.rnAll - A.rnAll - 1) AS AvgRowsBetween
FROM AccItem A
INNER JOIN AccItem B ON (A.itemId = B.itemId AND A.rnItem + 1 = B.rnItem)
June 16, 2009 at 1:07 pm
Thanks for all of the replies, and thanks andrewd. Your query seems to be what I am looking for.. I'm unable to test at the moment but will test it later on.
I'll post the results
Thanks again
June 16, 2009 at 1:50 pm
You might wish to cast to a float or decimal value before calculating the average.
;WITH AccItem AS (
SELECT itemId,
ROW_NUMBER() OVER (ORDER BY dt) AS rnAll,
ROW_NUMBER() OVER (PARTITION BY itemId ORDER BY dt) AS rnItem
FROM #tblItem
)
SELECT
A.itemId,
AVG(CAST(B.rnAll - A.rnAll - 1 AS float)) AS AvgRowsBetween
FROM AccItem A
INNER JOIN AccItem B ON (A.itemId = B.itemId AND A.rnItem + 1 = B.rnItem)
GROUP BY A.itemId
ORDER BY A.itemId
or
;WITH AccItem AS (
SELECT itemId,
ROW_NUMBER() OVER (ORDER BY dt) AS rnAll,
ROW_NUMBER() OVER (PARTITION BY itemId ORDER BY dt) AS rnItem
FROM #tblItem
)
SELECT
AVG(CAST(B.rnAll - A.rnAll - 1 AS float)) AS AvgRowsBetween
FROM AccItem A
INNER JOIN AccItem B ON (A.itemId = B.itemId AND A.rnItem + 1 = B.rnItem)
June 16, 2009 at 6:45 pm
WITH AccItem AS (
SELECT itemId,
ROW_NUMBER() OVER (ORDER BY dt) AS rnAll,
ROW_NUMBER() OVER (PARTITION BY itemId ORDER BY dt) AS rnItem
FROM #tblItem
)
SELECT
A.itemId,
AVG(CAST(B.rnAll - A.rnAll - 1 AS float)) AS AvgRowsBetween
FROM AccItem A
INNER JOIN AccItem B ON (A.itemId = B.itemId AND A.rnItem + 1 = B.rnItem)
GROUP BY A.itemId
ORDER BY A.itemId
This worked perfectly without any modification to the query.
Thank you so much.
Cheers,
Albert
June 16, 2009 at 8:57 pm
andrewd.smith (6/16/2009)
You might wish to cast to a float or decimal value before calculating the average.
Nicely done, Mr. Smith. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply