how many in common?
-
Hi
I have a table
Client_id id value
10 1 1
20 1 1
30 1 1
40 1 2
10 2 1
20 2 2
30 2 3
40 2 3
10 3 2
20 3 3
30 3 3
40 3 1
and i want a query from that table to show how many combinations of id and value each client has in common
so it would look something like
Client1 Client2 Common
10 20 1
10 30 1
10 40 0
20 30 2
20 40 0
30 40 1
so client_id 10 and client_id 20 have 1 combination in common which is id 1 and value 1
client_id 20 and client_id 30 have 2 in common which are id 1 and value 1 and id 3 and value 3
I hope that makes sense
thanks in advance
-
Hi,
This query gives the number of matches but has a problem: also returns [20,10] and doesn't return "unmatches" like [10,40]..
SELECT t1.client_id, t2.client_id, COUNT(1)
FROM aux_data t1
INNER JOIN aux_data t2 ON t1.client_id <> t2.client_id AND t1.id = t2.id AND t1.value = t2.value
GROUP BY t1.client_id, t2.client_id
ORDER BY t1.client_id, t2.client_id
Pedro
If you need to work better, try working less... -
Try this
WITH data (Client_id, id, value) AS (SELECT 10, 1, 1 UNION ALL
SELECT 20, 1, 1 UNION ALL
SELECT 30, 1, 1 UNION ALL
SELECT 40, 1, 2 UNION ALL
SELECT 10, 2, 1 UNION ALL
SELECT 20, 2, 2 UNION ALL
SELECT 30, 2, 3 UNION ALL
SELECT 40, 2, 3 UNION ALL
SELECT 10, 3, 2 UNION ALL
SELECT 20, 3, 3 UNION ALL
SELECT 30, 3, 3 UNION ALL
SELECT 40, 3, 1)
SELECT t1.Client_id, t2.Client_id, SUM(CASE WHEN t1.id=t2.id AND t1.value=t2.value THEN 1 ELSE 0 END)
FROM data t1
INNER JOIN data t2 ON t2.Client_id>t1.Client_id
GROUP BY t1.Client_id, t2.Client_id
ORDER BY t1.Client_id, t2.Client_id;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
DECLARE @Table TABLE (Client_id int, id int, value int)
INSERT INTO @Table VALUES
(10, 1, 1),
(20, 1, 1),
(30, 1, 1),
(40, 1, 2),
(10, 2, 1),
(20, 2, 2),
(30, 2, 3),
(40, 2, 3),
(10, 3, 2),
(20, 3, 3),
(30, 3, 3),
(40, 3, 1)
SELECT
CID1,
CID2,
SUM(inc) NumberInCommon
FROM
(
SELECT
t1.Client_id as CID1,
t2.Client_id AS CID2,
1 AS inc
FROM
@Table t1
INNER JOIN
@Table t2
ON
t1.id = t2.id
where
t1.value = t2.value
and
t1.Client_id <> t2.Client_id
and
t1.Client_id < t2.Client_id
) AS Dev1
group by
CID1,
CID2
-
Not sure why you've got some rows with Common=0 & not others..
But how about this?
--==== TEST DATA ======
if object_id('tempdb..#temp') is not null
drop table #temp;
create table #temp
(
Client_id int,
id int,
value int
);
insert #temp values ( 10, 1, 1 );
insert #temp values ( 20, 1, 1 );
insert #temp values ( 30, 1, 1 );
insert #temp values ( 40, 1, 2 );
insert #temp values ( 10, 2, 1 );
insert #temp values ( 20, 2, 2 );
insert #temp values ( 30, 2, 3 );
insert #temp values ( 40, 2, 3 );
insert #temp values ( 10, 3, 2 );
insert #temp values ( 20, 3, 3 );
insert #temp values ( 30, 3, 3 );
insert #temp values ( 40, 3, 1 );
select * from #temp;
--===== ALL ROWS IN MATRIX ========
with clients as
(
select distinct client_id
from #temp
),
matrix as
(
select a.client_id as Client1, b.client_id as Client2
from clients a
cross join clients b
),
basedata as
(
select a.Client_id as Client1, a.Id, a.Value, b.client_id as Client2
from #temp a
inner join #temp b on a.id=b.id and a.value=b.value
where b.client_id > a.client_id
),
data as
(
select Client1, Client2, count(*) as Common
from basedata
group by Client1, Client2
)
select m.Client1, m.Client2, ISNULL(d.Common, 0000) as Common
from matrix m
left outer join data d on d.Client1=m.Client1 and d.Client2=m.Client2
order by m.Client1, m.Client2
--===== ONLY ROWS WITH VALUES =====
select Client1, Client2, count(*) as Common
from
(
select a.Client_id as Client1, a.Id, a.Value, b.client_id as Client2
from #temp a
inner join #temp b on a.id=b.id and a.value=b.value
where b.client_id > a.client_id
) x
group by Client1, Client2
order by Client1, Client2
-
thansk for the replies
plenty to go off
-
Joining two answers gets what you want 🙂
SELECT t1.Client_id, t2.Client_id, SUM(CASE WHEN t1.id=t2.id AND t1.value=t2.value THEN 1 ELSE 0 END)
FROM aux_data t1
INNER JOIN aux_data t2 ON t1.Client_id < t2.Client_id
GROUP BY t1.Client_id, t2.Client_id
ORDER BY t1.Client_id, t2.Client_id;
If you need to work better, try working less...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply