Блог

У меня есть таблица с показаниями расхода газа и всеми видами данных, необходимых для анализа потребления газа. Я пытаюсь использовать SQL для выполнения вычислений и предоставления ответа, который я могу отправить генератору графиков.

Прямо сейчас я пытаюсь сохранить результат вычисления в виде переменной для дальнейшего использования в запросе: @corrConsGas . Само вычисление работает нормально и возвращает столбец с правильными данными, но переменная остается нулевой.

corrConsGas также, похоже, пропускает первый день диапазона. Я подтвердил это, сгруппировав и упорядочив результаты по годам, месяцам и дням. corrConsGas было 0.000 для первой даты. Я понятия не имею, почему.

 SELECT
   DATE_FORMAT(date, "%Y-%m") as "Month",

   -- Calculate avg gas consumption on non-heating days (when degrDays = 0), i.e. avg gas used for showering etc
   -- Uses data from >= 2018-06-11, as no decimals were reported before that date...
   @avgNHConsGas := (SELECT AVG(`consGas`) FROM `energyTST` WHERE degrDays = 0 AND consGas > 0 AND DATE >= '2018-06-11') AS "avgNHConsGas",

   -- Uncorrected values
   sum(`degrDays`) as "degrDays",
   sum(`consGas`) as "consGas",
   IF( SUM(`consGas`) > 0, ROUND( SUM(`consGas`) / SUM(`degrDays`), 3), 0 ) AS "consDD",


   -- Corrected gas consumption and consumption per degree day. Produces 0 instead of negative consumption.
   -- Skips first day in range...why???
   @corrConsGas := CASE
       WHEN SUM(`consGas`) - SUM(@avgNHConsGas) > 0 THEN ROUND(SUM(`consGas` - @avgNHConsGas), 3)
       ELSE 0
   END AS "corrConsGas",

   -- calculate corrected gas consumption per degree day.
   -- @corrGasCons == NULL...why???
   SUM(@corrConsGas) / SUM(`degrDays`) AS corrConsDD,
   @corrConsGas  2 AS "TEST",


   -- Calculate corrConsGas again to calculate corrected gas consumption per degree day (corrConsGasDD).
   IF( SUM(`consGas` - @avgNHConsGas) > 0, ROUND(SUM(`consGas` - @avgNHConsGas) / SUM(`degrDays`), 3), 0 ) AS "corrConsGasDD"


   FROM `energyTST`
   WHERE date >= '2018-02-01' AND date <= '2018-04-30'
   GROUP BY YEAR(date), MONTH(date)
   ORDER BY YEAR(date), MONTH(date)
  

Все даты хранятся как дата и форматируются как ГГГГ-ММ-ДД без данных о времени, все остальные данные хранятся в виде десятичной дроби (32,3).

На случай, если у кого-нибудь возникнет подобный вопрос; Я, наконец, решил его, используя INNER JOIN для включения вычислений в столбец вместо использования переменных:

 SELECT
   e1.date,

   -- Weighted degree days
   SUM(e2.wDegrDays) AS "wDegrDays",

   -- Gas usage based on daily usage minus average daily usage for showering
   SUM(IF(e1.consGas - e3.avgSU > 0, e1.consGas - e3.avgSU, 0)) AS "corrConsGas",

   -- Gas usage per degree day
   SUM(IF(e1.consGas - e3.avgSU > 0, e1.consGas - e3.avgSU, 0)) / SUM(e2.wDegrDays) AS "corrConsGasDD"

FROM 
   energyTST AS e1

   INNER JOIN
      (SELECT
         date,

         -- Degree days is the difference between daily mean temperature indoor (dmtI) and daily mean temperature outdoor (dmtO).
         -- They are weighed based on the month to account for weather influences (Nov-Feb * 1.1, Apr-Sept * 0.8)
         -- and only calculated when the dmtO is below the heating threshold, otherwise there are 0 degree days.
         CASE
            WHEN MONTH(date) IN (04, 05, 06, 07, 08, 09) THEN ROUND(IF(dmtO < ht, dmtI - dmtO, 0) * 0.8, 3)
            WHEN MONTH(date) IN (11, 12, 01, 02) THEN ROUND(IF(dmtO < ht, dmtI - dmtO, 0) * 1.1, 3)
            ELSE ROUND(IF(dmtO < ht, dmtI - dmtO, 0), 3)
         END AS "wDegrDays"

      FROM energyTST
      ) AS e2 ON e1.date = e2.date

   INNER JOIN
      (SELECT

         -- Average usage on non-heating days = average daily shower usage
         ROUND(AVG(consGas), 3) AS "avgSU"

      FROM energyTST
      WHERE degrDays = 0 AND consGas > 0 AND date >= '2018-06-11'
      ) AS e3

WHERE e1.date >= '2018-02-01' AND e1.date <= '2018-02-28'
GROUP BY YEAR(e1.date), MONTH(e1.date), DAY(e1.date)
ORDER BY YEAR(e1.date), MONTH(e1.date), DAY(e1.date)
  

Как указано в комментарии Пола, вы должны просто иметь возможность использовать подзапрос следующим образом:

 ... energyTST 
CROSS JOIN (
   SELECT AVG(`consGas`) AS "avgNHConsGas"
   FROM `energyTST` 
   WHERE degrDays = 0 AND consGas > 0 AND DATE >= '2018-06-11'
) AS a
  

а затем замените все ссылки на @avgNHConsGas на a.avgNHConsGas

Порядок вычисления и присвоения выражений, включающих переменные @ / сеанса, не всегда ясен; запросы, включающие агрегирование, являются одним из таких случаев.