Аритметика в SQL: изчисляеми колони и ROUND

Проблем

Колко струва този ред?

Счетоводството иска за всеки ред от поръчка колко струва: количеството по цената. В таблицата order_items има само двете числа:

SQLбаза: магазин

SELECT * FROM order_items WHERE order_id = 3;

В поръчка 3 има ябълки, 4 по 2,10 €, и прах за пране, 3 по 12,50 €. Да смяташ на ръка за 152 реда не е работа за човек. А да пазиш сумата в още една колона значи да я поддържаш вярна при всяка промяна.

Идеята

Колона, която се смята

В SELECT освен колони може да пишеш и изрази: сметки с колоните. Базата ги пресмята за всеки ред:

SQLбаза: магазин

SELECT order_id, product_id, quantity, unit_price,
       quantity * unit_price AS total
FROM order_items
WHERE order_id = 3;

Знаците са +, -, *, / и % (остатък при деление). Името на сметнатата колона е самият израз, затова ѝ даваш кратко име с AS. Таблицата не се променя: сметката е само в резултата.

По псевдонима може и да подредиш. Петте най-скъпи реда от всички поръчки:

SQLбаза: магазин

SELECT order_id, product_id, quantity * unit_price AS total
FROM order_items
ORDER BY total DESC
LIMIT 5;

Изразът може и без таблица. Тогава SELECT пресмята веднъж, като калкулатор. Умножението е преди събирането, а скобите сменят реда:

SQLбаза: магазин

SELECT 2 + 3 * 4, (2 + 3) * 4;

Пробвай

Цената с ДДС

Цените в базата са без ДДС. Покажи името, цената и цената с 20% ДДС в колона with_vat.

SQLбаза: магазин

SELECT name, price FROM products;

Решение

SELECT name, price, price * 1.2 AS with_vat FROM products;

Виж го

Оценката в проценти

Сайтът иска да показва оценките от 1 до 5 като проценти: 5 е 100%, 4 е 80%. Пусни:

SQLбаза: магазин

SELECT rating, rating / 5 * 100 AS percent FROM reviews;

1. Отзивът с оценка 4 излезе с 0%. Защо?Защото процентите се пишат с %Защото 4 / 5 при две цели числа е 0, а 0 * 100 е 0Защото оценката е NULL

Това е целочислено деление. Когато и двете числа са цели, резултатът също е цял:

SQLбаза: магазин

SELECT 5 / 2, 5 / 2.0, CAST(5 AS REAL) / 2;

Щом едното число е дробно (2.0) или е превърнато в дробно с CAST(… AS REAL), и делението е дробно. За процентите е достатъчно да умножиш първо, по 100.0:

SQLбаза: магазин

SELECT rating, rating * 100.0 / 5 AS percent FROM reviews;

При price този капан го няма: цената е дробно число (REAL). Пази се при колоните с цели числа, като rating, quantity и stock.

По-точно, за любопитните

PostgreSQL и SQL Server делят целите числа по същия начин: 5 / 2 е 2. MySQL прави обратното: там 5 / 2 е 2.5000, а за целочислено деление има DIV. Делението на нула в SQLite дава NULL, а в PostgreSQL е грешка.

Идеята

ROUND: до цент

Промоция: −15% на млечните. Новата цена е 85% от старата:

SQLбаза: магазин

SELECT name, price, price * 0.85, ROUND(price * 0.85, 2)
FROM products
WHERE category_id = 4;

Кисело мляко за 0,935 € не може да се плати. ROUND(x, 2) закръгля до две цифри след точката, тоест до цент. ROUND(x) закръгля до цяло число.

Погледни сиренето: 6,715 стана 6,71, а не 6,72. Защо, виж отдолу.

По-точно, за любопитните

Дробните числа в компютъра се пазят в двоична система, а там 0,1 няма точен запис, както 1/3 няма точен запис с десетични цифри. Затова:

SQLбаза: магазин

SELECT 0.1 + 0.2, 0.1 + 0.2 = 0.3, printf('%!.20f', 0.1 + 0.2);

На екрана е 0,3, но на сравнението базата казва „не“ (0). printf с %!.20f показва какво е записано наистина. Същото е със сиренето: 7,9 × 0,85 в паметта е 6,71499…, и ROUND честно го закръгля надолу.

Затова в истинските системи парите не се пазят като REAL. Пазят се в цели центове (1,20 € е 120), или в тип за точни десетични числа: NUMERIC(10, 2) или DECIMAL(10, 2) в PostgreSQL, MySQL и SQL Server. В SQLite такъв тип няма, затова там се ползват центове:

SQLбаза: магазин

SELECT ROUND(7.9 * 0.85, 2), ROUND(790 * 85 / 100.0);

С центове 790 × 85 е точно 67 150, а делението на 100 дава 671,5 цента, които се закръглят до 672, тоест 6,72 €.

Пробвай

Промоцията

Покажи името, цената и промоционалната цена на всички продукти. Промоционалната е price * 0.85, закръглена до цент, в колона sale_price.

SQLбаза: магазин

SELECT name, price, price * 0.85 FROM products;

Решение

SELECT name, price, ROUND(price * 0.85, 2) AS sale_price FROM products;

Упражнение

Две задачи

1. Застрахователят пита колко струва стоката в склада. Покажи петте продукта с най-голяма стойност на наличността (цена по наличност), най-голямата първа. Подаръчната карта не е стока на рафта: остави само продуктите с категория.

Име, наличност и stock_value, петте най-големи:

SQLбаза: магазин

Решение

SELECT name, stock, price * stock AS stock_value
FROM products
WHERE category_id IS NOT NULL
ORDER BY stock_value DESC
LIMIT 5;

2. Стоката пристига в кашони по 12 бройки. За всеки продукт с категория, който е в наличност, покажи колко пълни кашона са (boxes) и колко бройки остават извън тях (loose).

Име, наличност, boxes и loose:

SQLбаза: магазин

Решение

SELECT name, stock, stock / 12 AS boxes, stock % 12 AS loose
FROM products
WHERE stock > 0 AND category_id IS NOT NULL;

Тест

Провери се

1. Какво връща SELECT 7 / 2;?433,5

2. Променя ли SELECT price * 1.2 AS with_vat FROM products; цените в таблицата?Само ако има WHEREНе, сметката е само в резултата на заявкатаДа, цените стават с ДДС

3. Кое подрежда редовете на поръчките по стойност, най-голямата първа?Не може: по сметната колона не се подреждаORDER BY quantity DESCORDER BY total DESC, където total е псевдонимът на quantity * unit_price

4. Какво дава ROUND(2.345, 1)?2,32,352

Сметки в заявката

Бисквитки