CODEWITHSUNDEEP
mysql
alekssaff
alekssaff

Reputation: 167

MySQL select with group and one to many relations condition

For example have such structure:

CREATE TABLE clicks
    (`date` varchar(50), `sum` int, `id` int)
;
CREATE TABLE marks
    (`click_id` int, `name` varchar(50), `value` varchar(50))
;

where click can have many marks

So example data:

INSERT INTO clicks
    (`sum`, `id`, `date`)
VALUES
    (100, 1, '2017-01-01'),
    (200, 2, '2017-01-01')
    ;
INSERT INTO marks
    (`click_id`, `name`, `value`)
VALUES
    (1, 'utm_source', 'test_source1'),
    (1, 'utm_medium', 'test_medium1'),
    (1, 'utm_term', 'test_term1'),
    (2, 'utm_source', 'test_source1'),
    (2, 'utm_medium', 'test_medium1')
    ;

I need to get agregated values of click grouped by date which contains all of selected values. I make request:

select 
  c.date,
  sum(c.sum)
from clicks as c
left join marks as m ON m.click_id = c.id
where 
(m.name = 'utm_source' AND m.value='test_source1') OR
(m.name = 'utm_medium' AND m.value='test_medium1') OR
(m.name = 'utm_term' AND m.value='test_term1')
group by date

and get 2017-01-01 = 700, but I want to get 100 which means that only click 1 has all of marks. Or if condition will be 

(m.name = 'utm_source' AND m.value='test_source1') OR
(m.name = 'utm_medium' AND m.value='test_medium1')

I need to get 300 instead of 600

I found answer in getting distinct click_id by first query and then sum and group by date with condition whereIn, but on real database which is very large and has id as uuid this request executes extrimely slow. Any advices how to get it work propely?

Upvotes: 2

Views: 93

Answers (3)

alekssaff
alekssaff

Reputation: 167

I found the right way myself, which works on large amounts of data The main goal is to make request generate one table with subqueries(conditions) which do not depend on amount of data in results, so the best way is:

select 
  c.date,
  sum(c.sum)
from clicks as c
join marks as m1 ON m1.click_id = c.id
join marks as m2 ON m2.click_id = c.id
join marks as m3 ON m3.click_id = c.id
where 
(m1.name = 'utm_source' AND m1.value='test_source1') AND
(m2.name = 'utm_medium' AND m2.value='test_medium1') AND
(m3.name = 'utm_term' AND m3.value='test_term1')
group by date

So we need to make as many joins as many conditions we have

Upvotes: 1

cdaiga
cdaiga

Reputation: 4937

You're getting 700 because the join generates multiple rows for the different IDs. There are 3 rows in the mark table with ID=1 and sum=100 and there are two rows with ID=2 and sum=200. On doing the join where shall have 3 rows with sum=100 and 2 rows with sum=200, so adding these sum gives 700. To fix this you have to aggregate on the click_id too as illustrated below:

select 
  c.date,
  sum(c.sum)
from clicks as c
inner join (select * from marks where (name = 'utm_source' AND     
    value='test_source1') OR (name = 'utm_medium' AND value='test_medium1')  
    OR (name = 'utm_term' AND value='test_term1')
    group by click_id) as m
ON m.click_id = c.id
group by c.date;

DEMO SQL FIDDLE

Upvotes: 1

Mittal Patel
Mittal Patel

Reputation: 2762

You can achieve it using below queries: When there are the three conditions then you have to pass the HAVING count(*) >= 3

SELECT cc.DATE
    ,sum(cc.sum)
FROM clicks AS cc
INNER JOIN (
    SELECT id
    FROM clicks AS c
    LEFT JOIN marks AS m ON m.click_id = c.id
    WHERE (
            m.NAME = 'utm_source'
            AND m.value = 'test_source1'
            )
        OR (
            m.NAME = 'utm_medium'
            AND m.value = 'test_medium1'
            )
        OR (
            m.NAME = 'utm_term'
            AND m.value = 'test_term1'
            )
    GROUP BY id
    HAVING count(*) >= 3
    ) AS t ON cc.id = t.id
GROUP BY cc.DATE

When there are the three conditions then you have to pass the HAVING count(*) >= 2

SELECT cc.DATE
    ,sum(cc.sum)
FROM clicks AS cc
INNER JOIN (
    SELECT id
    FROM clicks AS c
    LEFT JOIN marks AS m ON m.click_id = c.id
    WHERE (
            m.NAME = 'utm_source'
            AND m.value = 'test_source1'
            )
        OR (
            m.NAME = 'utm_medium'
            AND m.value = 'test_medium1'
            )
    GROUP BY id
    HAVING count(*) >= 2
    ) AS t ON cc.id = t.id
GROUP BY cc.DATE

Demo: http://sqlfiddle.com/#!9/fe571a/35

Hope this works for you...

Upvotes: 1

Related Questions