Блог

Главная - Вопросы по программированию - Причина, по которой запрос SQLite возвращает неверное значение с sum

Причина, по которой запрос SQLite возвращает неверное значение с sum

#database #sqlite

#База данных #sqlite

Вопрос:

Я столкнулся со странным поведением в SQLite при выполнении запроса, и я хотел бы понять, почему такое поведение происходит.

Когда я запускаю следующий запрос, скидка рассчитывается неправильно, что делает суммированное значение неверным. 

 SELECT sum (quantity * price) - (sum (quantity * price)*(discount/100)) as total
FROM [orderProducts]
JOIN [order] ON [order].id = orderProducts.order_id

Когда скидка равна 0, запрос ведет себя так, как ожидалось, т.е. Сумма заказа суммируется правильно. Однако при наличии установленной скидки общее значение неверно. Скидка, похоже, применяется случайным образом к строкам, когда происходит сумма. Я получаю правильное поведение, когда я делаю какую-то творческую группировку

 SELECT sum (total) FROM (SELECT sum (quantity * price) - (sum (quantity * price)*(discount/100)) as total
FROM [orderProducts]
JOIN [order] ON [order].id = orderProducts.order_id
GROUP BY [order].id)

Итак, мой вопрос в том, почему это имеет такое большое значение? Что происходит в первом запросе, из-за которого скидка применяется, по-видимому, случайным образом?

Для проверки запросов вы можете использовать этот оператор для создания таблиц

 create table orderProducts 
(
id int NOT NULL,
order_id NOT NULL,
quantity int(3),
price double NOT NULL,
primary key (id),
foreign key (order_id) references [order]
);

create table order
(
id int NOT NULL,
discount double NOT NULL,
primary key (id)
);

И для добавления данных вы можете использовать

 insert into order (id, discount) values (1, 10.0);
insert into order (id, discount) values (2, 0.0);

insert into orderProducts (id, order_id, quantity, price) values (1, 1, 1, 20);
insert into orderProducts (id, order_id, quantity, price) values (2, 2, 1, 50);

Ожидаемый результат выполнения запроса sum равен 68, но фактический результат равен 70

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

1. Покажите ваши определения таблиц и образцы данных (в виде инструкций sql create table и insert для упрощения импорта), а также результаты, которые вы хотите получить из этих данных.

2. Но я уверен, что проблема в том, что discount — это столбец с целочисленной привязкой, поэтому вы выполняете целочисленное деление, когда вам нужна плавающая точка.

3. @shawn Я добавил таблицу и вставил запросы для вас

4. Использование ключевых слов sql в качестве имен столбцов — это настоящая боль… в любом случае, double тип преобразуется real в сходство с sqlite, так что это очень много для этой конкретной идеи. (Я получил 63.0 в результате вашего первого запроса, кстати …)

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

Ответ №1:

Если вы ожидаете получить 68 из ваших выборочных данных, это делает трюк:

 SELECT sum(quantity * price) - sum((quantity * price) * (discount / 100)) AS total
FROM orderProducts
JOIN "order" ON "order".id = orderProducts.order_id;

В вашем исходном запросе используется sum(quantity * price) * (discount / 100) — то есть он умножает сумму итогов всех строк на процент, вместо того, чтобы умножать общую сумму каждой строки на процент скидки этой строки и суммировать эти числа.

Также обратите внимание на то, что я использую двойные кавычки вокруг имени order таблицы вместо скобок; кавычки — это обычный, стандартный SQL-способ экранирования идентификаторов; скобки — это функция MS-SQL, которую Sqlite поддерживает для некоторой степени совместимости.