Блог

Главная - Вопросы по программированию - Не удается запустить код MySQL, который, похоже, работает для других

Не удается запустить код MySQL, который, похоже, работает для других

#mysql #sql #mysql-workbench

#mysql #sql #mysql-workbench

Вопрос:

Цель кода — выбрать месяц, SaleID, Total и Рост. Я могу отобразить Month, SaleID и Total, но не могу заставить Growth работать, потому что он всегда вычисляется из первой строки. Что я делаю не так?

Я попытался настроить переменные, эмулировать LAG() , PREV, CURRENT, NEXT, чтобы получить строку, которую должен использовать расчет, но он не будет регистрировать собственные функции.

 CREATE VIEW SalesTemp
    AS
        SELECT 
            DATE_FORMAT(Sales.SaleDate, "%Y-%m") AS Month,
            Sales.SaleID,
            Sales.Total
        FROM Sales
        WHERE SaleDate BETWEEN '2018-04-00' AND '2040-00-00' 
        GROUP BY DATE_FORMAT(Sales.SaleDate, "%Y-%m"); 

SELECT * FROM SalesTemp;

DROP VIEW IF EXISTS PercentageGrowth;
CREATE VIEW PercentageGrowth 
    AS
        SELECT 
            DATE_FORMAT(Sales.SaleDate, "%Y-%m") AS Month,
            Sales.SaleID,
            Sales.Total,
            CONCAT(ROUND(((Sales.Total) - SalesTemp.Total) / (SELECT SalesTemp.Total FROM SalesTemp GROUP BY DATE_FORMAT(SalesTemp.Month, "%Y-%m")) * 100, 2), "%") AS Growth
        FROM Sales, SalesTemp
        GROUP BY DATE_FORMAT(Sales.SaleDate, "%Y-%m");

SELECT * FROM PercentageGrowth;

DROP VIEW PercentageGrowth;
DROP VIEW SalesTemp;

Я хочу, чтобы он отображал рост компании путем вычисления ((newValue — oldValue) / oldValue).

Поскольку я не могу связать изображения, я использую ascii, каков результат. Что я получаю от ВЫБОРА сейчас:

  -------------------------------------- 
|  Month  | SaleID |  Total  | Growth  |
| ------- | ------ | ------- | ------- |
| 2018-04 |    1   | 310.46  | 00.00%  |
| 2018-05 |    3   | 2160.62 | 595.54% |
| 2018-06 |    6   | 1087.89 | 250.21% |
| 2018-07 |    14  | 2314.54 | 645.09% |
 --------------------------------------

Я хочу, чтобы он сказал:

  -------------------------------------- 
|  Month  | SaleID |  Total  | Growth  |
| ------- | ------ | ------- | ------- |
| 2018-04 |    1   | 310.46  | 00.00%  |
| 2018-05 |    3   | 2160.62 | 595.54% |
| 2018-06 |    6   | 1087.89 | -49.64% |
| 2018-07 |    14  | 2314.54 | 112.76% |
 --------------------------------------

Комментарии:

1. Зачем вам нужно эмулировать? MySQL 8 теперь поддерживает оконные функции , включая LAG и CTE , чтобы избежать удаления и создания представлений.

2. Более того, ваши совокупные запросы не группируются по продажам. Идентификатор , который, к сожалению, разрешен в MySQL, используя ТОЛЬКО режим ПОЛНОЙ ГРУППЫ , отключен. Такая функция оказывает плохую услугу новым пользователям SQL, поскольку такие операторы недопустимы и могут привести к сбою в любой другой базе данных.

Ответ №1:

В настоящее время вы перекрестно соединяете свои пары таблиц во всех SaleID и во всех месяцах с отформатированными датами, и на это еще больше влияют ваши неясные агрегации.

Предполагая, что вы используете MySQL 8 , рассмотрите пару CTE, которые включают LAG в себя одно смещение ваших совокупных итогов за месяц:

 WITH cte1 AS 
  (SELECT DATE_FORMAT(Sales.SaleDate, "%Y-%m") AS `Month`,
          Sales.SaleID,
          SUM(Sales.Total) AS `Total_Sales`
   FROM Sales
   WHERE SaleDate BETWEEN '2018-04-00' AND '2040-00-00' 
   GROUP BY 
          DATE_FORMAT(Sales.SaleDate, "%Y-%m"),
          Sales.SaleID
  ),
    cte2 AS
  (SELECT *, 
          LAG(`Total_Sales`) OVER (PARTITION BY `SaleID` 
                                   ORDER BY `Month`) AS `Lag_Total_Sales`
   FROM cte1) 

SELECT `Month`, `SaleID`, `Total_Sales`,
       CONCAT(
              ROUND(
                    (`Total_Sales` - `Lag_Total_Sales`) / `Lag_Total_Sales` 
                    , 2) * 100
             , '%') AS `Growth`
FROM cte2

Для MySQL 5.7 или менее рассмотрите возможность самостоятельного объединения подзапроса, который явно объединяет SaleID и любую дату в прошлом месяце, нормализуя все даты до первого из соответствующих месяцев.

 SELECT DATE_FORMAT(curr.FirstMonth, "%Y-%m") AS `Month`,
       curr.SaleID,
       curr.Total_Sales,
       CONCAT(
              ROUND((`curr`.Total_Sales - `prev`.Total_Sales) / `prev`.Total_Sales
                    , 2)*100  
             , '%') AS `Growth`
FROM
   (SELECT DATE_ADD(LAST_DAY(DATE_SUB(SaleDate, INTERVAL 1 MONTH))
                    , INTERVAL 1 DAY) As FirstMonth,
           SaleID,
           SUM(`Total`) As `Total_Sales`
    FROM Sales
    GROUP BY 
           DATE_ADD(LAST_DAY(DATE_SUB(SaleDate, INTERVAL 1 MONTH))
                    , INTERVAL 1 DAY),
           SaleID
   ) AS `curr`
LEFT JOIN
   (SELECT DATE_ADD(LAST_DAY(DATE_SUB(SaleDate, INTERVAL 1 MONTH))
                    , INTERVAL 1 DAY) As FirstMonth,
           SaleID,
           SUM(`Total`) As `Total_Sales`
    FROM Sales
    GROUP BY 
           DATE_ADD(LAST_DAY(DATE_SUB(SaleDate, 
                                      INTERVAL 1 MONTH)) 
                    , INTERVAL 1 DAY),
           SaleID
   ) AS `prev`
ON `curr`.SaleID = `prev`.SaleID   
AND `curr`.FirstMonth - INTERVAL 1 MONTH = `prev`.FirstMonth
AND `curr`.FirstMonth BETWEEN '2018-04-00' AND '2040-00-00'

Демо -версия Rext (версия MySQL 5.7)