Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Subselect vs. LEFT JOIN

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von striker_2k, 18 November 2013.

  1. striker_2k

    striker_2k Neuer Benutzer

    Hallo zusammen,

    Ich habe eine Tabelle mit Zählerständen, die ich 1x die Woche eingebe. Die Zähler haben eigene IDs und ich habe eine Abfrage die mir alle Zähler nebeneinander aus der selben Tabelle anzeigt (sortiert nach Woche).

    Dabei wird auch gleich die Differenz zw. den Wochen ausgerechent und mit angezeigt.

    Das Problem ist, dass ich viele Subselects habe und bei der Umstellung auf Join scheitere.
    Im Subselect bekomme ich richtigerweise keine Werte für einen Zähler, der in einer Woche keine Einträge hat.
    Wenn ich das jetzt mit LEFT JOIN versuche, zeigt er nur die kleinste Menge an.

    Ich dachte eigentlich, dass LEFT JOIN dann führend wäre und NULL bei Zählern ohne Wert setehen würde.

    Liege ich hier komplett falsch oder funktioniert LEFT JOIN nicht auf der selben Tabelle?

    Tabelle:
    Code:
    CREATE TABLE IF NOT EXISTS `readings` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `meter_id` int(11) NOT NULL,
      `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `value` float NOT NULL,
      PRIMARY KEY (`id`)
    )
    Abfrage Subselect, funktioniert wie gewollt:
    Code:
    SELECT yearweek(r1.time) as week,date(r1.time) as date,
    r1.value as gas,
    round((r1.value - (select r.value from energy.readings r where r.meter_id = r1.meter_id and r.time < r1.time order by r.time desc limit 1)),3) as diff_gas,
    (select r.value  from readings r  where r.time = r1.time and r.meter_id = (select meter_id from meter where active = 1 and name = 'Solarausbeute')) as solar,
    ((select r.value  from readings r  where r.time = r1.time and r.meter_id = (select meter_id from meter where active = 1 and name = 'Solarausbeute')) -
    (select r2.value from readings r2 where r2.meter_id = (select meter_id from meter where active = 1 and name = 'Solarausbeute') and r2.time < r1.time order by r2.time desc limit 1)) as diff_solar,
    r3.value as strom ,
    round((r3.value - (select r.value from readings r where r.meter_id = r3.meter_id and r.time < r3.time order by r.time desc limit 1)),1) as diff_strom,
    r4.value as wasser ,
    round((r4.value - (select r.value from energy.readings r where r.meter_id = r4.meter_id and r.time < r4.time order by r.time desc limit 1)),3) as diff_wasser
    FROM energy.readings r1
    join energy.readings r3 on r3.time = r1.time
    join energy.readings r4 on r4.time = r1.time
    WHERE
    r1.meter_id in (select meter_id from meter where name = 'Gas') and
    r3.meter_id = (select meter_id from meter where active = 1 and name = 'Strom') and
    r4.meter_id = (select meter_id from meter where active = 1 and name = 'Wasser')
    order by r1.time desc
    
    Abfrage Join, schneided ab:
    Code:
    SELECT yearweek(r1.time) as week,date(r1.time) as date,
    r1.value as gas, 
    round((r1.value - (select r.value from energy.readings r where r.meter_id = r1.meter_id and r.time < r1.time order by r.time desc limit 1)),3) as diff_gas,
    r2.value as solar, 
    round((r2.value - (select r.value from energy.readings r where r.meter_id = r2.meter_id and r.time < r2.time order by r.time desc limit 1)),3) as diff_solar,
    r3.value as strom ,
    round((r3.value - (select r.value from readings r where r.meter_id = r3.meter_id and r.time < r3.time order by r.time desc limit 1)),1) as diff_strom,
    r4.value as wasser ,
    round((r4.value - (select r.value from energy.readings r where r.meter_id = r4.meter_id and r.time < r4.time order by r.time desc limit 1)),3) as diff_wasser
    FROM energy.readings r1
    join energy.readings r2 on r2.time = r1.time
    join energy.readings r3 on r3.time = r1.time
    join energy.readings r4 on r4.time = r1.time
    WHERE
    r1.meter_id = 5 and
    r2.meter_id = 6 and
    r3.meter_id = 2 and
    r4.meter_id = 3
    order by r1.time desc
    
    Werte:
    Code:
    INSERT INTO `readings` (`id`, `meter_id`, `time`, `value`) VALUES
    (193, 5, '2013-05-05 20:00:00', 1719.85),
    (194, 5, '2013-05-12 20:00:00', 1730.7),
    (195, 5, '2013-05-19 20:00:00', 1740.2),
    (196, 5, '2013-05-26 20:00:00', 1758.35),
    (197, 5, '2013-06-02 20:00:00', 1779.15),
    (198, 5, '2013-06-09 20:00:00', 1790.92),
    (199, 5, '2013-06-16 20:00:00', 1796.52),
    (200, 5, '2013-06-23 20:00:00', 1801.6),
    (201, 5, '2013-06-30 20:00:00', 1811.04),
    (202, 5, '2013-07-07 20:00:00', 1815.44),
    (203, 5, '2013-07-14 20:00:00', 1819),
    (204, 5, '2013-07-21 20:00:00', 1819.39),
    (205, 5, '2013-07-28 20:00:00', 1823.1),
    (206, 5, '2013-08-04 20:00:00', 1827.42),
    (207, 5, '2013-08-11 20:00:00', 1829.11),
    (208, 5, '2013-08-18 20:00:00', 1829.37),
    (209, 5, '2013-08-25 20:00:00', 1831.39),
    (210, 5, '2013-09-01 20:00:00', 1833.49),
    (211, 5, '2013-09-08 20:00:00', 1836.18),
    (212, 5, '2013-09-15 20:00:00', 1837.64),
    (213, 5, '2013-09-22 20:00:00', 1856.26),
    (214, 5, '2013-09-29 20:00:00', 1871.96),
    (215, 5, '2013-10-06 20:00:00', 1886.24),
    (216, 5, '2013-10-13 20:00:00', 1896.69),
    (217, 5, '2013-10-20 20:00:00', 1915.52),
    (218, 5, '2013-10-27 20:00:00', 1921.38),
    (219, 5, '2013-11-03 20:00:00', 1937.89),
    (411, 2, '2013-05-05 20:00:00', 1760.6),
    (412, 2, '2013-05-12 20:00:00', 1809.6),
    (413, 2, '2013-05-19 20:00:00', 1852.3),
    (414, 2, '2013-05-26 20:00:00', 1898.7),
    (415, 2, '2013-06-02 20:00:00', 1940.2),
    (416, 2, '2013-06-09 20:00:00', 1978.8),
    (417, 2, '2013-06-16 20:00:00', 2025.1),
    (418, 2, '2013-06-23 20:00:00', 2064.1),
    (419, 2, '2013-06-30 20:00:00', 2119.7),
    (420, 2, '2013-07-07 20:00:00', 2171.8),
    (421, 2, '2013-07-14 20:00:00', 2207.1),
    (422, 2, '2013-07-21 20:00:00', 2234.4),
    (423, 2, '2013-07-28 20:00:00', 2291),
    (424, 2, '2013-08-04 20:00:00', 2331.8),
    (425, 2, '2013-08-11 20:00:00', 2364.8),
    (426, 2, '2013-08-18 20:00:00', 2397.5),
    (427, 2, '2013-08-25 20:00:00', 2435.7),
    (428, 2, '2013-09-01 20:00:00', 2470.1),
    (429, 2, '2013-09-08 20:00:00', 2497.7),
    (430, 2, '2013-09-15 20:00:00', 2528.5),
    (431, 2, '2013-09-22 20:00:00', 2573.3),
    (432, 2, '2013-09-29 20:00:00', 2617.9),
    (433, 2, '2013-10-06 20:00:00', 2669.5),
    (434, 2, '2013-10-13 20:00:00', 2715.1),
    (435, 2, '2013-10-20 20:00:00', 2758.6),
    (436, 2, '2013-10-27 20:00:00', 2807.5),
    (437, 2, '2013-11-03 20:00:00', 2855.2),
    (626, 3, '2013-05-05 20:00:00', 51.034),
    (627, 3, '2013-05-12 20:00:00', 52.751),
    (628, 3, '2013-05-19 20:00:00', 54.104),
    (629, 3, '2013-05-26 20:00:00', 55.662),
    (630, 3, '2013-06-02 20:00:00', 57.129),
    (631, 3, '2013-06-09 20:00:00', 58.365),
    (632, 3, '2013-06-16 20:00:00', 60.035),
    (633, 3, '2013-06-23 20:00:00', 61.111),
    (634, 3, '2013-06-30 20:00:00', 63.218),
    (635, 3, '2013-07-07 20:00:00', 64.455),
    (636, 3, '2013-07-14 20:00:00', 65.808),
    (637, 3, '2013-07-21 20:00:00', 65.825),
    (638, 3, '2013-07-28 20:00:00', 67.958),
    (639, 3, '2013-08-04 20:00:00', 69.414),
    (640, 3, '2013-08-11 20:00:00', 70.941),
    (641, 3, '2013-08-18 20:00:00', 72.152),
    (642, 3, '2013-08-25 20:00:00', 73.533),
    (643, 3, '2013-09-01 20:00:00', 74.938),
    (644, 3, '2013-09-08 20:00:00', 75.891),
    (645, 3, '2013-09-15 20:00:00', 76.144),
    (646, 3, '2013-09-22 20:00:00', 77.461),
    (647, 3, '2013-09-29 20:00:00', 78.705),
    (648, 3, '2013-10-06 20:00:00', 80.152),
    (649, 3, '2013-10-13 20:00:00', 81.253),
    (650, 3, '2013-10-20 20:00:00', 82.553),
    (651, 3, '2013-10-27 20:00:00', 83.658),
    (652, 3, '2013-11-03 20:00:00', 85.052),
    (657, 5, '2013-11-10 20:00:00', 1966.54),
    (658, 2, '2013-11-10 20:00:00', 2909.7),
    (659, 3, '2013-11-10 20:00:00', 86.496),
    (660, 6, '2013-08-11 20:00:00', 106),
    (661, 6, '2013-08-18 20:00:00', 103),
    (662, 6, '2013-08-25 20:00:00', 120),
    (663, 6, '2013-09-01 20:00:00', 145),
    (664, 6, '2013-09-08 20:00:00', 150),
    (665, 6, '2013-09-15 20:00:00', 54),
    (666, 6, '2013-09-22 20:00:00', 85),
    (667, 6, '2013-09-29 20:00:00', 199),
    (668, 6, '2013-10-06 20:00:00', 280),
    (669, 6, '2013-10-13 20:00:00', 106),
    (670, 6, '2013-10-20 20:00:00', 142),
    (671, 6, '2013-10-27 20:00:00', 175),
    (672, 6, '2013-11-03 20:00:00', 75),
    (673, 6, '2013-11-10 20:00:00', 25),
    (674, 5, '2013-11-17 20:00:00', 2004.05),
    (675, 2, '2013-11-17 20:00:00', 2962.9),
    (676, 3, '2013-11-17 20:00:00', 87.769),
    (677, 6, '2013-11-17 20:00:00', 91);
     
  2. striker_2k

    striker_2k Neuer Benutzer

    Hier noch die andere Tabelle

    Code:
    --
    -- Tabellenstruktur für Tabelle `meter`
    --
    
    CREATE TABLE IF NOT EXISTS `meter` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `meter_id` int(11) NOT NULL,
      `name` varchar(20) NOT NULL,
      `unit` varchar(20) NOT NULL,
      `cost_year_id` int(11) NOT NULL,
      `cost_unit_id` int(11) NOT NULL,
      `active` tinyint(1) NOT NULL,
      `valid_from` datetime NOT NULL,
      `valid_to` datetime NOT NULL DEFAULT '2999-12-31 23:59:59',
      `version` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
    
    --
    -- Daten für Tabelle `meter`
    --
    
    INSERT INTO `meter` (`id`, `meter_id`, `name`, `unit`, `cost_year_id`, `cost_unit_id`, `active`, `valid_from`, `valid_to`, `version`) VALUES
    (1, 1, 'Gas', 'm³', 1, 2, 0, '2005-04-01 00:00:00', '2012-10-21 00:00:00', 1),
    (2, 2, 'Strom', 'kWh', 3, 4, 1, '2012-07-01 00:00:00', '2999-12-31 23:59:59', 1),
    (3, 3, 'Wasser', 'm³', 5, 6, 1, '2012-07-01 00:00:00', '2999-12-31 23:59:59', 1),
    (4, 4, 'Abwasser', 'm³', 7, 8, 1, '2012-07-01 00:00:00', '2999-12-31 23:59:59', 1),
    (5, 5, 'Gas', 'm³', 1, 2, 1, '2012-07-01 00:00:00', '2999-12-31 23:59:59', 1),
    (6, 6, 'Solarausbeute', '', 0, 0, 1, '2012-10-21 00:00:00', '2999-12-31 23:59:59', 1);
     
  3. akretschmer

    akretschmer Datenbank-Guru

    Also, ich hab mal Deine Tabelle via Gutten&Berg, ähm, Copy&Paste, kopiert:

    Code:
    test=*# select * from reading limit 10;
    id  | meter_id |  time  |  value
    -----+----------+---------------------+---------
    193 |  5 | 2013-05-05 20:00:00 | 1719.85
    194 |  5 | 2013-05-12 20:00:00 |  1730.7
    195 |  5 | 2013-05-19 20:00:00 |  1740.2
    196 |  5 | 2013-05-26 20:00:00 | 1758.35
    197 |  5 | 2013-06-02 20:00:00 | 1779.15
    198 |  5 | 2013-06-09 20:00:00 | 1790.92
    199 |  5 | 2013-06-16 20:00:00 | 1796.52
    200 |  5 | 2013-06-23 20:00:00 |  1801.6
    201 |  5 | 2013-06-30 20:00:00 | 1811.04
    202 |  5 | 2013-07-07 20:00:00 | 1815.44
    (10 rows)
    
    Wenn ich Dich richtig verstehe, willst Du in etwa sowas:

    Code:
    test=*# select time, value_2, lead(value_2) over (order by time desc), (value_2 - lead(value_2) over (order by time desc))::numeric(4,1) as diff_2, value_3, lead(value_3) over (order by time desc), (value_3 - lead(value_3) over (order by time desc))::numeric(4,1) as diff_3, value_5,lead(value_5) over (order by time desc), (value_5 - lead(value_5) over (order by time desc))::numeric(4,1) as diff_6, value_6, lead(value_6) over (order by time desc), (value_6 - lead(value_6) over (order by time desc))::numeric(4,1) as diff_6 from (select time, sum(case when meter_id = 2 then value else null end) as value_2, sum(case when meter_id = 3 then value else null end) as value_3, sum(case when meter_id=5 then value else null end) as value_5, sum(case when meter_id=6 then value else null end) as value_6 from reading group by time order by time desc) foo;
      time  | value_2 |  lead  | diff_2 | value_3 |  lead  | diff_3 | value_5 |  lead  | diff_6 | value_6 | lead | diff_6
    ---------------------+---------+--------+--------+---------+--------+--------+---------+---------+--------+---------+------+--------
    2013-11-17 20:00:00 |  2962.9 | 2909.7 |  53.2 |  87.769 | 86.496 |  1.3 | 2004.05 | 1966.54 |  37.5 |  91 |  25 |  66.0
    2013-11-10 20:00:00 |  2909.7 | 2855.2 |  54.5 |  86.496 | 85.052 |  1.4 | 1966.54 | 1937.89 |  28.6 |  25 |  75 |  -50.0
    2013-11-03 20:00:00 |  2855.2 | 2807.5 |  47.7 |  85.052 | 83.658 |  1.4 | 1937.89 | 1921.38 |  16.5 |  75 |  175 | -100.0
    2013-10-27 20:00:00 |  2807.5 | 2758.6 |  48.9 |  83.658 | 82.553 |  1.1 | 1921.38 | 1915.52 |  5.9 |  175 |  142 |  33.0
    2013-10-20 20:00:00 |  2758.6 | 2715.1 |  43.5 |  82.553 | 81.253 |  1.3 | 1915.52 | 1896.69 |  18.8 |  142 |  106 |  36.0
    2013-10-13 20:00:00 |  2715.1 | 2669.5 |  45.6 |  81.253 | 80.152 |  1.1 | 1896.69 | 1886.24 |  10.5 |  106 |  280 | -174.0
    2013-10-06 20:00:00 |  2669.5 | 2617.9 |  51.6 |  80.152 | 78.705 |  1.4 | 1886.24 | 1871.96 |  14.3 |  280 |  199 |  81.0
    2013-09-29 20:00:00 |  2617.9 | 2573.3 |  44.6 |  78.705 | 77.461 |  1.2 | 1871.96 | 1856.26 |  15.7 |  199 |  85 |  114.0
    2013-09-22 20:00:00 |  2573.3 | 2528.5 |  44.8 |  77.461 | 76.144 |  1.3 | 1856.26 | 1837.64 |  18.6 |  85 |  54 |  31.0
    2013-09-15 20:00:00 |  2528.5 | 2497.7 |  30.8 |  76.144 | 75.891 |  0.3 | 1837.64 | 1836.18 |  1.5 |  54 |  150 |  -96.0
    2013-09-08 20:00:00 |  2497.7 | 2470.1 |  27.6 |  75.891 | 74.938 |  1.0 | 1836.18 | 1833.49 |  2.7 |  150 |  145 |  5.0
    2013-09-01 20:00:00 |  2470.1 | 2435.7 |  34.4 |  74.938 | 73.533 |  1.4 | 1833.49 | 1831.39 |  2.1 |  145 |  120 |  25.0
    2013-08-25 20:00:00 |  2435.7 | 2397.5 |  38.2 |  73.533 | 72.152 |  1.4 | 1831.39 | 1829.37 |  2.0 |  120 |  103 |  17.0
    2013-08-18 20:00:00 |  2397.5 | 2364.8 |  32.7 |  72.152 | 70.941 |  1.2 | 1829.37 | 1829.11 |  0.3 |  103 |  106 |  -3.0
    2013-08-11 20:00:00 |  2364.8 | 2331.8 |  33.0 |  70.941 | 69.414 |  1.5 | 1829.11 | 1827.42 |  1.7 |  106 |  |
    2013-08-04 20:00:00 |  2331.8 |  2291 |  40.8 |  69.414 | 67.958 |  1.5 | 1827.42 |  1823.1 |  4.3 |  |  |
    2013-07-28 20:00:00 |  2291 | 2234.4 |  56.6 |  67.958 | 65.825 |  2.1 |  1823.1 | 1819.39 |  3.7 |  |  |
    2013-07-21 20:00:00 |  2234.4 | 2207.1 |  27.3 |  65.825 | 65.808 |  0.0 | 1819.39 |  1819 |  0.4 |  |  |
    2013-07-14 20:00:00 |  2207.1 | 2171.8 |  35.3 |  65.808 | 64.455 |  1.4 |  1819 | 1815.44 |  3.6 |  |  |
    2013-07-07 20:00:00 |  2171.8 | 2119.7 |  52.1 |  64.455 | 63.218 |  1.2 | 1815.44 | 1811.04 |  4.4 |  |  |
    2013-06-30 20:00:00 |  2119.7 | 2064.1 |  55.6 |  63.218 | 61.111 |  2.1 | 1811.04 |  1801.6 |  9.4 |  |  |
    2013-06-23 20:00:00 |  2064.1 | 2025.1 |  39.0 |  61.111 | 60.035 |  1.1 |  1801.6 | 1796.52 |  5.1 |  |  |
    2013-06-16 20:00:00 |  2025.1 | 1978.8 |  46.3 |  60.035 | 58.365 |  1.7 | 1796.52 | 1790.92 |  5.6 |  |  |
    2013-06-09 20:00:00 |  1978.8 | 1940.2 |  38.6 |  58.365 | 57.129 |  1.2 | 1790.92 | 1779.15 |  11.8 |  |  |
    2013-06-02 20:00:00 |  1940.2 | 1898.7 |  41.5 |  57.129 | 55.662 |  1.5 | 1779.15 | 1758.35 |  20.8 |  |  |
    2013-05-26 20:00:00 |  1898.7 | 1852.3 |  46.4 |  55.662 | 54.104 |  1.6 | 1758.35 |  1740.2 |  18.1 |  |  |
    2013-05-19 20:00:00 |  1852.3 | 1809.6 |  42.7 |  54.104 | 52.751 |  1.4 |  1740.2 |  1730.7 |  9.5 |  |  |
    2013-05-12 20:00:00 |  1809.6 | 1760.6 |  49.0 |  52.751 | 51.034 |  1.7 |  1730.7 | 1719.85 |  10.9 |  |  |
    2013-05-05 20:00:00 |  1760.6 |  |  |  51.034 |  |  | 1719.85 |  |  |  |  |
    (29 rows)
    
    Die Spaltennamen habe ich jetzt mal selber gemacht. Die Spalte lead ist jeweils der Wert der Vorwoche, diff_N die Differenz zur Vorwoche.

    Ist halt schon praktisch und einfach machbar in fast allen Datenbanken außer MySQL, denn ausgerechnet das kann keine Window-Funktionen, die Du hier ja suchst.

    (ich nutze PostgreSQL)

    Vielleicht zwingt Dich ja keiner, MySQL zu nehmen, und ein Wechsel wäre machbar?

    Edit:
    select time, value_2, lead(value_2) over (order by time desc), (value_2 - lead(value_2) over (order by time desc))::numeric(4,1) as diff_2, value_3, lead(value_3) over (order by time desc), (value_3 - lead(value_3) over (order by time desc))::numeric(4,1) as diff_3, value_5,lead(value_5) over (order by time desc), (value_5 - lead(value_5) over (order by time desc))::numeric(4,1) as diff_5, value_6, lead(value_6) over (order by time desc), (value_6 - lead(value_6) over (order by time desc))::numeric(4,1) as diff_6 from (select time, sum(case when meter_id = 2 then value else null end) as value_2, sum(case when meter_id = 3 then value else null end) as value_3, sum(case when meter_id=5 then value else null end) as value_5, sum(case when meter_id=6 then value else null end) as value_6 from reading group by time order by time desc) foo;

    ich hatte da noch einen Fipptehler...
     
    Zuletzt bearbeitet: 18 November 2013
  4. striker_2k

    striker_2k Neuer Benutzer

    Danke für die schnelle Antwort.

    Ich werde mal PostgreSQL testen.

    Die LEAD Funktion kannte ich noch nicht. Werde mich da mal weiter einlesen.

    Aber um auf die JOIN Frage zurückzukommen:
    es müsste doch generell auch mit nem JOIN gehen, oder ist die WHERE clause da einfach falsch?

    VG
     
  5. akretschmer

    akretschmer Datenbank-Guru

    Es gibt ganz sicher ganz viele Wege zum Ziel. Um mit Vorgänger/Nachfolger zu arbeiten sind aber diese Window-Funktionen extrem praktisch.
    Kann aber auch sein, daß der @ukulele noch kommt und einen anderen Weg zeigt, ...
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden