December 28, 2018 at 9:06 am
Hello,
I have this tricky number/analytical problem, which I think is illustrated best with a stock market analogy (simplified):
Put easy, this table contains some companies and stocks. Both can be rated and there can be more than one rating values. A flag tells, if the rating is relevant/reliable and can be used.
It's not required to distinguish between companies and stocks. That's why I put both into one table. Companies will be treated just like stocks to rate them. However, if a stock is not rated in a relevant way, then the appropriate company rating will be used.
I failed at the more-complex combinations so far…
Rules:
No relevant (stock) rating available?
--> Proceed with company rating and apply this to all of the group
One relevant (stock) rating available?
--> Use it
Many relevant (stock) ratings available?
--> Use the one from rank two (second-best rating/second smallest value)
For the dear helpers, I have provided a table with some test data (not all possible combinations)
Many Thanks in Advance 🙂
drop table if exists ratingDemo;
create table ratingDemo (
[id] bigint identity (1, 1) not null,
[type] varchar(10) not null,
[name] varchar(50) not null,
[stockUID] int null,
[companyUID] int not null,
[relevant] bit not null,
[rating] int not null
constraint [pk_decisionDemo] primary key clustered
(
[id] asc
)
)
;
-- company ratings maybe used "stand-alone" or as a reference from stock ratings
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: one rating, not relevant', 100, 0, 1);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: one rating, relevant', 200, 1, 1);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, none relevant', 300, 0, 1);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, none relevant', 300, 0, 2);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, none relevant', 300, 0, 3);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, one relevant', 400, 0, 1);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, one relevant', 400, 0, 2);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, one relevant', 400, 1, 3);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, many relevant', 500, 0, 1);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, many relevant', 500, 1, 2);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, many relevant', 500, 1, 3);
-- just for completion: if there are more than two relevant ratings, always pick the second-best: 2 of 1,2,3
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, all relevant', 600, 1, 1);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, all relevant', 600, 1, 2);
insert into ratingDemo ([type], [name], [companyUID], [relevant], [rating]) values ('company', 'company: many ratings, all relevant', 600, 1, 3);
-- to keep things simple: all stocks are bound to company 500
-- since the stock is not rated in a way relevant to us, pick the according company's rating (rules apply)
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: one rating, not relevant', 1000, 500, 0, 1);
-- the only rating available to this stock is relevant, use it (easiest case)
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: one rating, relevant', 2000, 500, 1, 1);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, none relevant', 3000, 500, 0, 1);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, none relevant', 3000, 500, 0, 2);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, none relevant', 3000, 500, 0, 3);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, one relevant', 4000, 500, 0, 1);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, one relevant', 4000, 500, 0, 2);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, one relevant', 4000, 500, 1, 3);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, many relevant', 5000, 500, 0, 1);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, many relevant', 5000, 500, 1, 2);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, many relevant', 5000, 500, 1, 3);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, all relevant', 6000, 500, 1, 1);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, all relevant', 6000, 500, 1, 2);
insert into ratingDemo ([type], [name], [stockUID], [companyUID], [relevant], [rating]) values ('stock', 'stock: many ratings, all relevant', 6000, 500, 1, 3);
-- expected result
select
stock.*,
case stock.[stockUID]
when 1000 then 0
when 2000 then 1
when 3000 then 2 -- second best, relevant rating of companyUID = 500
when 4000 then 3 -- apply the one relevant to all of group (stock)
when 5000 then 3 -- apply the second-best relevant to all of group (stock)
when 6000 then 3 -- same as above
end as expectedResult_rating,
case stock.[stockUID]
when 1000 then 'find appropriate company rating'
when 2000 then 'use this'
when 3000 then 'find appropriate company rating (and apply to all records of the same stockUID)'
when 4000 then 'use the relevant one for all records of the same stockUID'
when 5000 then 'use the second-best relevant for all records of the same stockUID'
when 6000 then 'use the second-best relevant for all records of the same stockUID' -- same as above
end as usedRule
from ratingDemo stock
where
stock.[type] = 'stock'
order by
stock.[stockUID],
stock.[relevant] desc,
stock.[rating]
/*
-- non-successfull attempt with a union
-- tried to use window functions to gather additional information for later decisions
select
ratingData.*,
case
-- one relevant stock rating: use it
when (ratingData.relevant = 1) and (ratingData.ratings_available = 1) then ratingData.rating
-- more than one relevant stock rating: use second-best
-- apply [rating] from [rating_order] = 2 to all stocks with that UID ???
end as finalRating
from (
select
stock.[type],
stock.[name],
stock.[stockUID],
stock.[companyUID],
stock.[relevant],
stock.[rating],
-- number of relevant ratings present
count(stock.[stockUID]) over(
partition by
stock.[stockUID]
) as ratings_available,
-- assign a rank to the rating's value (to pick the second-highest grade)
row_number() over(
partition by
stock.[stockUID]
order by
stock.[rating] asc
) as rating_order
from ratingDemo stock
where
stock.[type] = 'stock'
and stock.[relevant] = 1
union all
select
company.[type],
company.[name],
company.[companyUID],
company.[companyUID],
company.[relevant],
company.[rating],
-- number of relevant ratings present
count(company.[companyUID]) over(
partition by
company.[companyUID]
) as ratings_available,
-- assign a rank to the rating's value (to pick the second-highest grade)
row_number() over(
partition by
company.[companyUID]
order by
company.[rating] asc
) as rating_order
from ratingDemo company
where
company.[type] = 'company'
and company.[relevant] = 1
) ratingData
*/
December 28, 2018 at 2:41 pm
WITH stockRatings AS (
SELECT stockUID,
rating,
stockRatingCount = COUNT(*) OVER (PARTITION BY stockUID),
stockRatingOrder = ROW_NUMBER() OVER (PARTITION BY stockUID ORDER BY rating ASC)
FROM dbo.ratingDemo
WHERE type = 'stock' AND relevant = 1 ),
companyRatings AS (
SELECT companyUID,
rating,
companyRatingCount = COUNT(*) OVER (PARTITION BY companyUID),
companyRatingOrder = ROW_NUMBER() OVER (PARTITION BY companyUID ORDER BY rating ASC)
FROM dbo.ratingDemo
WHERE type = 'company' AND relevant = 1 )
SELECT r.name, r.stockUID, r.companyUID, rawRating = r.rating, finalRating = ISNULL(s.rating, c.rating),
ruleUsed = CASE WHEN s.stockRatingOrder = 2 THEN 'Second-best relevant stock rating'
WHEN s.stockRatingCount = 1 THEN 'Only relevant stock rating'
WHEN c.companyRatingOrder = 2 THEN 'Second-best relevant company rating'
WHEN c.companyRatingCount = 1 THEN 'Only relevant company rating'
END
FROM dbo.ratingDemo r
LEFT JOIN stockRatings s ON s.stockUID = r.stockUID AND ( s.stockRatingCount = 1 OR s.stockRatingOrder = 2 )
LEFT JOIN companyRatings c ON c.companyUID = r.companyUID AND ( c.companyRatingCount = 1 OR c.companyRatingOrder = 2 )
WHERE r.type = 'stock'
December 28, 2018 at 3:20 pm
Hey Scott
Thanks a lot. I was quite sure, there's a more elegant way to solve this. I will check your solution. Mine is ridiculously long, complicated and hard to read. It even uses a view as a temporary helper. The only difference: i make the final list distinct. Every group (stock or company) will appear just once, either marked as relevant or not, but never mixed.
Internal View:
create view companyRating
as
select
companyRatings.[type],
companyRatings.[companyUID],
companyRatings.[name],
companyRatings.[relevant],
companyRatings.[rating_value]
from (
-- the sub-query garantes that every company will be listed either
-- with one or more relevant ratings or one or more irrelevant ratings,
-- never mixed; therefore we only need to check how many ratings are present later
select
ratingData.*,
-- that's the rating which counts for its group
case
-- one rating
when (ratingData.ratings_available = 1) then 1
-- many ratings
when (ratingData.ratings_available > 1) and (ratingData.ratings_order = 2) then 1
end as rating_candidate
from (
select
[type],
[name],
[companyUID],
[relevant],
[rating] as rating_value,
-- number of ratings present
count([companyUID]) over(
partition by
[companyUID]
) as ratings_available,
-- rating values order
row_number() over(
partition by
[companyUID]
order by
[rating]
) as ratings_order
from ratingDemo
where
[type] = 'company'
and [relevant] = 1
union all
-- add irrelevant only company ratings
-- (companies that do not have any relevant ratings)
select
[type],
[name],
[companyUID],
[relevant],
[rating] as rating_value,
-- number of ratings present
count([companyUID]) over(
partition by
[companyUID]
) as ratings_available,
-- rating values order
row_number() over(
partition by
[companyUID]
order by
[rating]
) as ratings_order
from ratingDemo
where
[type] = 'company'
and [relevant] = 0
and [companyUID] not in (
select
[companyUID]
from ratingDemo
where
[type] = 'company'
and [relevant] = 1
)
) ratingData
) companyRatings
where
companyRatings.[rating_candidate] = 1
Final Result (experimental):
select
stockRating.[type],
stockRating.[name],
stockRating.[stockUID],
stockRating.[companyUID],
stockRating.[relevant],
stockRating.[rating_value]
from (
select
ratingData.[type],
ratingData.[name],
ratingData.[stockUID],
ratingData.[companyUID],
ratingData.[relevant],
-- use company rating if there's no relevant stock rating
case
when (ratingData.[relevant] = 0) then companyRating.rating_value
else ratingData.rating_value
end as rating_value,
-- that's the rating which counts for its group
case
-- one rating
when (ratingData.ratings_available = 1) then 1
-- many ratings
when (ratingData.ratings_available > 1) and (ratingData.ratings_order = 2) then 1
end as rating_candidate
from (
-- make sure that every group will be listed either
-- with one or more relevant ratings or one or more irrelevant ratings,
-- but never mixed;
-- therefore we only need to check how many ratings are present later
select
[type],
[name],
[stockUID],
[companyUID],
[relevant],
[rating] as rating_value,
-- number of ratings present
count([stockUID]) over(
partition by
[stockUID]
) as ratings_available,
-- rating values order
row_number() over(
partition by
[stockUID]
order by
[rating]
) as ratings_order
from ratingDemo
where
[type] = 'stock'
and [relevant] = 1
union all
-- add irrelevant only ratings
-- (groups that do not have any relevant ratings)
select
[type],
[name],
[stockUID],
[companyUID],
[relevant],
[rating] as rating_value,
-- number of ratings present
count([stockUID]) over(
partition by
[stockUID]
) as ratings_available,
-- rating values order
row_number() over(
partition by
[stockUID]
order by
[rating]
) as ratings_order
from ratingDemo
where
[type] = 'stock'
and [relevant] = 0
and [stockUID] not in (
select
[stockUID]
from ratingDemo
where
[type] = 'stock'
and [relevant] = 1
)
) ratingData
-- use company rating's as fall-back/reference
left outer join companyRating
on companyRating.companyUID = ratingData.companyUID
) stockRating
where
stockRating.rating_candidate = 1
union all
-- add companies as 'stand-alone' ratings
select
[type],
[name],
null as [stockUID],
[companyUID],
[relevant],
[rating_value]
from companyRating
December 29, 2018 at 12:28 am
I was able to re-phrase and extend my query with your help 🙂
with companyRatings as (
select
ratingData.*,
-- that's the rating which counts for its group
case
-- one rating
when (ratingData.ratings_available = 1) then 1
-- many ratings
when (ratingData.ratings_available > 1) and (ratingData.ratings_order = 2) then 1
end as rating_candidate
from (
-- make sure that every group will be listed either
-- with one or more relevant ratings or one or more irrelevant ratings,
-- but never mixed;
-- therefore we only need to check how many ratings are present later
select
[type],
[name],
[companyUID],
[relevant],
[rating] as rating_value,
-- number of ratings present
count([companyUID]) over(
partition by
[companyUID]
) as ratings_available,
-- rating values order
row_number() over(
partition by
[companyUID]
order by
[rating] asc
) as ratings_order
from ratingDemo
where
[type] = 'company'
and [relevant] = 1
-- add irrelevant only ratings
-- (groups that do not have any relevant ratings)
union all
select
[type],
[name],
[companyUID],
[relevant],
[rating] as rating_value,
-- number of ratings present
count([companyUID]) over(
partition by
[companyUID]
) as ratings_available,
-- rating values order
row_number() over(
partition by
[companyUID]
order by
[rating]
) as ratings_order
from ratingDemo
where
[type] = 'company'
and [relevant] = 0
and [companyUID] not in (
select
[companyUID]
from ratingDemo
where
[type] = 'company'
and [relevant] = 1
)
) ratingData
),
stockRatings as (
select
ratingData.[type],
ratingData.[name],
ratingData.[stockUID],
ratingData.[companyUID],
ratingData.[relevant],
-- use company rating if there's no relevant stock rating
case
when (ratingData.[relevant] = 0) then companyRating.rating_value
else ratingData.rating_value
end as rating_value,
-- that's the rating which counts for its group
case
-- one rating
when (ratingData.ratings_available = 1) then 1
-- many ratings
when (ratingData.ratings_available > 1) and (ratingData.ratings_order = 2) then 1
end as rating_candidate
from (
-- make sure that every group will be listed either
-- with one or more relevant ratings or one or more irrelevant ratings,
-- but never mixed;
-- therefore we only need to check how many ratings are present later
select
[type],
[name],
[stockUID],
[companyUID],
[relevant],
[rating] as rating_value,
-- number of ratings present
count([stockUID]) over(
partition by
[stockUID]
) as ratings_available,
-- rating values order
row_number() over(
partition by
[stockUID]
order by
[rating]
) as ratings_order
from ratingDemo
where
[type] = 'stock'
and [relevant] = 1
union all
-- add irrelevant only ratings
-- (groups that do not have any relevant ratings)
select
[type],
[name],
[stockUID],
[companyUID],
[relevant],
[rating] as rating_value,
-- number of ratings present
count([stockUID]) over(
partition by
[stockUID]
) as ratings_available,
-- rating values order
row_number() over(
partition by
[stockUID]
order by
[rating]
) as ratings_order
from ratingDemo
where
[type] = 'stock'
and [relevant] = 0
and [stockUID] not in (
select
[stockUID]
from ratingDemo
where
[type] = 'stock'
and [relevant] = 1
)
) ratingData
-- use company rating's as fall-back/reference
left outer join companyRating
on companyRating.companyUID = ratingData.companyUID
)
-- filter & merge final result
select
[type],
[name],
[stockUID],
[companyUID],
[relevant],
[rating_value]
from stockRatings sr
where
sr.rating_candidate = 1
-- add companies as 'stand-alone' ratings
union all
select
[type],
[name],
null as [stockUID],
[companyUID],
[relevant],
[rating_value]
from companyRatings cr
where
cr.rating_candidate = 1
order by
stockUID,
companyUID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply