Subselect vs. LEFT JOIN

[striker_2k]

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:

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:

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:

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:

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);

 

[striker_2k]

Hier noch die andere Tabelle

--
-- 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);

 

[akretschmer]

Hier noch die andere Tabelle

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

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:

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...

 

[striker_2k]

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

 

[akretschmer]

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

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, ...