Блог

У меня есть 2 таблицы:

 table: transaction:
====================
id  billed_date   amount
 1  2016-09-30      5
 2  2016-10-04      15
 3  2016-10-06      10

table: report_date
====================
transaction_id    report_date
      1            2016-10-01
 

Я хочу:

• Создайте отчет, в котором суммируется сумма всех транзакций за октябрь 2016 года
• Основывается на дате отчета, а не на дате выставления счета
• Если дата отчета не задана, она основывается на billed_date
• В приведенном выше примере я хочу, чтобы результат был равен 30 (а не 25).

Затем я пишу:

Первый:

 SELECT 
   sum(t.amount), 
   CASE WHEN d.report_date IS NOT NULL THEN d.report_date ELSE t.billed_date END AS new_date
FROM 
   transaction t LEFT JOIN report_date d ON t.id = d.transaction_id
WHERE new_date BETWEEN '2016-10-01' AND '2016-10-30'
 

Второй:

 SELECT sum(amount) FROM 
 (SELECT t.amount,
    CASE WHEN d.report_date IS NOT NULL THEN d.report_date ELSE t.billed_date END AS date
    FROM transaction t LEFT JOIN report_date d ON t.id = d.transaction_id
 ) t
WHERE t.date BETWEEN '2016-10-01' AND '2016-10-30'
 

Результат:

Первый:

• Неизвестный столбец ‘new_date’ в предложении ‘where’
• Если я заменю ‘new_date’ на ‘date’: результат = 25 (исключить id = 1)

Второй:

• результат = 30 => Правильно, но в моем случае, когда в таблице транзакций около 30 тыс. записей, процесс идет слишком медленно.

Кто-нибудь может мне помочь?

Прежде всего — часть

 CASE WHEN d.report_date IS NOT NULL THEN d.report_date ELSE t.billed_date END
 

может быть записано короче как

 COALESCE(d.report_date, t.billed_date)
 

или как

 IFNULL(d.report_date, t.billed_date)
 

В вашем первом запросе вы используете псевдоним столбца в предложении WHERE, что недопустимо. Вы можете исправить это, переместив выражение за псевдонимом в предложение WHERE:

 SELECT sum(t.amount)
FROM transaction t LEFT JOIN report_date d ON t.id = d.transaction_id
WHERE COALESCE(d.report_date, t.billed_date) BETWEEN '2016-10-01' AND '2016-10-30'
 

Это почти то же самое, что и ваше собственное решение.

Ваш второй запрос выполняется медленно, потому что MySQL должен сохранять результат подзапроса (30 тыс. строк) во временной таблице. Пытаясь оптимизировать его, вы получите то же решение, что и выше.

Однако, если у вас есть индексы transaction.billed_date , и report_date.report_date этот запрос все еще не может их использовать. Чтобы использовать индексы, вы можете разделить запрос на две части:

Записи с отчетом (будут использовать report_date.report_date индекс):

 SELECT sum(amount)
FROM transaction t JOIN report_date d ON id = transaction_id 
WHERE d.report_date BETWEEN '2016-10-01' AND '2016-10-30'
 

Записи без отчета (будут использовать transaction.billed_date индекс):

 SELECT sum(amount)
FROM transaction t LEFT JOIN report_date d ON id = transaction_id 
WHERE d.report_date IS NULL AND t.billed_dateBETWEEN '2016-10-01' AND '2016-10-30'
 

Оба запроса могут использовать индекс. Вам просто нужно суммировать результаты, что также можно сделать, объединив два запроса:

 SELECT (
    SELECT sum(amount)
    FROM transaction t JOIN report_date d ON id = transaction_id 
    WHERE d.report_date BETWEEN '2016-10-01' AND '2016-10-30'
)   (
    SELECT sum(amount)
    FROM transaction t LEFT JOIN report_date d ON id = transaction_id 
    WHERE d.report_date IS NULL AND t.billed_dateBETWEEN '2016-10-01' AND '2016-10-30'
) AS sum_amount
 

Я, наконец, нашел решение с помощью моего брата:

 SELECT sum(amount)
FROM transaction t LEFT JOIN report_date d ON id = transaction_id 
WHERE (report_date BETWEEN '2016-10-01' AND '2016-10-30') OR (report_date IS NULL AND billed_date BETWEEN '2016-10-01' AND '2016-10-30')
 

Спасибо за заботу обо мне!

Заполняется table: report_date отсутствующими значениями из table: transaction: регистра?

 SELECT id FROM report_date WHERE report_date BETWEEN '2016-10-01' AND '2016-10-30';

INSERT INTO report_date SELECT id, billed_date FROM transaction WHERE billed_date BETWEEN '2016-10-01' AND '2016-10-30' AND id NOT IN (ids_from previous_query);

SELECT sum(t.amount) FROM transaction LEFT JOIN report_date d ON (t.id = d.transaction_id) WHERE d.report_date BETWEEN '2016-10-01' AND '2016-10-30';
 

Ваш второй запрос правильный, нет необходимости переписывать запрос. Но я должен сказать вам одну вещь, которая вам очень поможет при работе с тысячами / миллионами записей. Мы также сосредоточились на некоторых других вещах. Потому что, когда ваша таблица содержит большое количество данных (в тысячах и миллионах) записей, для выполнения запроса требуется время. Это также может привести к блокировке, возможно, проблема с блокировкой запроса или удалением базы данных. Чтобы избежать этой проблемы, вы просто создаете индекс одного столбца. Создайте индекс для этого столбца, который действует / используется в предложениях where. Как и в вашем случае, вы можете создать индекс для столбца billed_date из таблицы транзакций. Потому что ваш результат основан на таблице транзакций. Для получения более подробной информации о том, как создать индекс в mysql / phpmyadmin, вы можете взять ссылку из этого http://www.yourwebskills.com/dbphpmyadmintable.php ссылка.

В какой-то момент я столкнулся с такой же проблемой, после чего я создал ИНДЕКС по столбцу. Сейчас я имею дело с миллионами записей, использующих mysql.