Търсите най-ефективната заявка за избор

Имам таблица и се нуждая от резултата в различни редове, където трябва да съществуват различни ключови стойности в една колона.

Получавам тази таблица веднъж на ден чрез REST API от друга система и изглежда в моята MySQL DB така (опростено):

+----+------+------------+-------+
| ID | type | date       | value |
+----+------+------------+-------+
|  1 | A    | 2019-06-01 |     1 |
|  2 | B    | 2019-06-01 |     2 |
|  3 | A    | 2019-06-02 |     4 |
|  4 | B    | 2019-06-03 |     5 |
|  9 | A    | 2019-06-09 |    11 |
| 10 | B    | 2019-06-09 |    14 |
| 11 | A    | 2019-06-24 |    99 |
+----+------+------------+-------+

Сега имам нужда от избор, който води до всички само там, където съществува стойност за тип A и тип B на една и съща дата. Резултатът трябва да изглежда така:

+------------+-------+--------+----+------+------------+-------+
| date       | typeA | valueA | ID | type | date       | value |
+------------+-------+--------+----+------+------------+-------+
| 2019-06-01 | A     |      1 |  2 | B    | 2019-06-01 |     2 |
| 2019-06-09 | A     |     11 | 10 | B    | 2019-06-09 |    14 |
+------------+-------+--------+----+------+------------+-------+
  • Първа идея:

Използването на подселекти... вероятно е лоша идея за големи таблици... но не съм сигурен.

SELECT * from 
(SELECT date as dateA, value as valueA from V1 where type = 'A') AS subA,
(SELECT date as dateB, value as valueB from V1 where type = 'B') AS subB
WHERE dateA = dateB
  • 2-ра идея:

Създаване на две временни таблици и след това към съединение. Но се нуждае от време за създаване на две таблици и обединяването на двете нови временни таблици.

CREATE TEMPORARY table tA SELECT date, type as typeA, value as valueA from V1 WHERE type = 'A';
CREATE TEMPORARY table tB SELECT date, type as typeB, value as valueB from V1 WHERE type = 'B';

SELECT * from tA 
INNER JOIN tB on tA.date = tB.date;
  • 3-та идея:

Създаване само на временна таблица и използване на това за свързване с помощта на основната таблица:

CREATE TEMPORARY table tB SELECT * from V1 WHERE type = 'B';

SELECT * from V1 
INNER JOIN tB on V1.date = tB.date
where V1.type = 'A'

Коя от идеите ми е най-подходяща за голяма маса или има по-добро решение.

Благодаря предварително.


mysql sql query-optimization
person Urs von Ballmoos    schedule 30.06.2019    source източник
comment
В идеалния случай се нуждаем от изход от SHOW CREATE TABLE table, тъй като трябва да знаем типовете данни и индексите, за да ви дадем възможно най-ефективната заявка за избор за вашата съществуваща структура   -  person Raymond Nijland    schedule 30.06.2019

Отговори (4)


arrow_upward
2
arrow_downward

Нуждаете се от вътрешно присъединяване

select select v1.date,v1.type as typeA, v1.value as valueA, 
       v2.id, v2.type as typeB, v2.date, v2.value
  from v v1 join v v2    -- v is table name
    on v1.date = v2.date
    and v1.type = 'A' and v2.type = 'B';

+------------+-------+--------+----+------+------------+-------+
| date       | typeA | valueA | ID | type | date       | value |
+------------+-------+--------+----+------+------------+-------+
| 2019-06-01 | A     |      1 |  2 | B    | 2019-06-01 |     2 |
| 2019-06-09 | A     |     11 | 10 | B    | 2019-06-09 |    14 |
+------------+-------+--------+----+------+------------+-------+

Демо

person Barbaros Özhan    schedule 30.06.2019

arrow_upward
1
arrow_downward

един ефективен начин може да се основава на вътрешно свързване между датата с различен брой = 2 и таблицата itesel един път за ype A и един tyme за тип b

    select t.date, t1.type typeA, t1.value valueA, t2.type typeB, t2.value valueB, 
    from (
        select  date
        from  my_table  
        group by  date  
        having count(distinct type) = 2
    ) t 
    inner join  my_table t1 on t1.date = t.date and type='A'
    inner join  my_table t2 on t2.date = t.date and type='B'
person scaisEdge    schedule 30.06.2019

arrow_upward
1
arrow_downward

Това може би не е най-очевидната заявка за писане.
Но може да има смисъл, тъй като се ориентирате към дата и обединявате типовете с дата.
Ефективността ще зависи много от използвания(ите) индекс(и), които са неизвестни.

SELECT 
 *
FROM (

  SELECT 
     date
   , MAX(CASE WHEN type = 'A' THEN type END) AS  typeA
   , MAX(CASE WHEN type = 'A' THEN value END) AS  valueA
  FROM 
   your_table
  WHERE
   type = 'A' 
  GROUP BY 
   date  
) AS a_type 
INNER JOIN (

  SELECT 
     date
   , MAX(CASE WHEN type = 'B' THEN type END) AS  typeB
   , MAX(CASE WHEN type = 'B' THEN value END) AS  valueB  
  FROM 
   your_table
  WHERE
   type = 'B' 
  GROUP BY 
   date   
) AS b_type
ON
 a_type.date = b_type.date

Резултат

| date       | typeA | valueA | date       | typeB | valueB |
| ---------- | ----- | ------ | ---------- | ----- | ------ |
| 2019-06-01 | A     | 1      | 2019-06-01 | B     | 2      |
| 2019-06-09 | A     | 11     | 2019-06-09 | B     | 14     |

вижте демо

person Raymond Nijland    schedule 30.06.2019
comment
Множество извлечени таблици вероятно ще бъдат много неефективни. - person Rick James; 01.07.2019

arrow_upward
1
arrow_downward

Ако всяка дата може да има само една от всеки тип (т.е. не може да има 2 „A“ или 2 „B“ за една и съща дата), тогава можете да групирате по датата и да използвате условно агрегиране, за да получите стойностите за различните видове.

select
    date,
    max(case when type = 'A' then id end) idA,
    max(case when type = 'A' then value end) valueA,
    max(case when type = 'B' then id end) idB,
    max(case when type = 'B' then value end) valueB
from V1
where type in ('A', 'B') -- only necessary if other types exist
group by date
having count(distinct type) = 2

http://sqlfiddle.com/#!9/69b9f/1

person FuzzyTree    schedule 30.06.2019