February 13, 2008 at 6:43 pm
Hello. I have the following table and sample data:
Id | CacheString
-----------------
1 | 'Cache miss: Entity 1'
2 | 'Cache hit: Entity 1'
3 | 'Cache miss: Entity 4'
4 | 'Cache miss: Entity 3'
5 | 'Cache hit: Entity 10'
What I am trying to do is generate a quick report of the "Cache hit ratio". I cannot change how the data goes into this table, so I cannot parse the Cache string into different columns. I would like to see the count of 'Cache hit's divided by the Sum of cache entries grouped by the 'Entity'.
For example, I would like to see:
Entity | Hit Ratio
-----------------
'Entity 1' | .5
'Entity 3' | 0
'Entity 4' | 0
'Entity 10' | 1
Any help is appreciated.
Regards,
Aaron
February 13, 2008 at 9:15 pm
Aaron,
This should do it... also, take a look at how I created the test table and data... would help a lot if you could post sample data like that in the future, please.
[font="Courier New"]--===== Create and populate a table variable with test data...
     -- THIS IS NOT PART OF THE SOLUTION!!!!
DECLARE @yourtable TABLE
        (
        ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        CacheString VARCHAR(40)
        )
 INSERT INTO @yourtable (CacheString)
 SELECT 'Cache miss: Entity 1' UNION ALL
 SELECT 'Cache hit: Entity 1' UNION ALL
 SELECT 'Cache miss: Entity 4' UNION ALL
 SELECT 'Cache miss: Entity 3' UNION ALL
 SELECT 'Cache hit: Entity 10'
--===== Solve the problem with an on-the-fly parse...
 SELECT Entity = 'Entity'
               + STR(CAST(SUBSTRING(CacheString,CHARINDEX(': ',CacheString)+9,40) AS INT),3),
        HitRatio = STR(SUM(CASE WHEN LEFT(CacheString,10) = 'Cache hit:' THEN 1.0 ELSE 0.0 END) / COUNT(*) , 5,1)
   FROM @yourtable
  GROUP BY 'Entity'
         + STR(CAST(SUBSTRING(CacheString,CHARINDEX(': ',CacheString)+9,40) AS INT),3)
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2008 at 9:29 pm
Looks like you beat me to it Jeff. My solution is a little different than yours and I dont know how the performance is different but there is more than one way to skin a cat.
DECLARE @t TABLE(
Id INT,
CacheString VARCHAR(25)
)
INSERT INTO @t
SELECT 1 , 'Cache miss: Entity 1' UNION ALL
SELECT 2 , 'Cache hit: Entity 1' UNION ALL
SELECT 3 , 'Cache miss: Entity 4' UNION ALL
SELECT 4 , 'Cache miss: Entity 3' UNION ALL
SELECT 5 , 'Cache hit: Entity 10'
SELECT a.Entitity,AVG(a.hit)
FROM(
SELECT
CASE WHEN CacheString LIKE '%miss%' THEN
RIGHT(CacheString,LEN(CacheString) - 18)
ELSE
RIGHT(CacheString,LEN(CacheString) - 17)
END AS [Entitity],
CASE WHEN CacheString LIKE '%hit%' THEN
CAST(1 AS DECIMAL(5,3))
ELSE
CAST(0 AS DECIMAL(5,3))
END AS [hit]
FROM @t
) AS a
GROUP BY a.Entitity
February 13, 2008 at 9:33 pm
I pasted your solution in Jeff and it seems both queries use the same execution plan.
February 13, 2008 at 11:00 pm
Agreed... basically the same... I've got an extra "Compute Scalar" 'cause of the formatting I did...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2008 at 12:59 am
Thank you both for your help! Jeff, I will do that in the future.
-Aaron
February 14, 2008 at 9:29 pm
Thanks, Aaron...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply