Sum qty что это
Получение итоговых значений
Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций. Стандартом предусмотрены следующие агрегатные функции:
|
Найти минимальную и максимальную цену на персональные компьютеры:
Результатом будет единственная строка, содержащая агрегатные значения:
|
Найти имеющееся в наличии количество компьютеров, выпущенных производителем А
В результате получим
Найти количество имеющихся различных моделей ПК, выпускаемых производителем А.
Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице РС (то есть имеющихся в продаже).
даст следующий результат
Как расшифровать qty, ctns, lbs, pcs, g.w, n.w в упаковочном листе?
Издание сопроводительных документов на перевозимый груз – неукоснительное требование законодательства. В международных контейнерных перевозках одним из таких обязательных документов является packing list. В обиходе по-русcки – пакинг лист (он же – упаковочный лист).
Заполняется данный документ, как правило на английском языке и для начинающих коммерсантов не всегда понятны сокращения английских слов в тексте. Надеемся, что наша шпаргалка поможет расшифровать аббревиатуру, используемую в пакинг листах.
Табл. Расшифровка аббревиатур в пакинг листе
Аббревиатура | Слово (англ.) | Перевод | Пример |
bnd, bndl | bundle | связка, пачка | 352 bndls / 352 связки |
cbm | cubic meter> | кубический метр | 20,21 CBM / 20,21 куб.м. |
ctn, ctns | carton | коробка | 650 CTNS / 650 коробок |
dim, dims | dimensions | размеры | Dims: 140x50x45 cm / Размер:140x50x45 см |
g.w | gross weight | вес Брутто | G.W. / Брутто |
kgs | kilogram | килограмм | 910 KGS / 910 кг |
lbs | pounds | фунты (мера веса) | 1500 LBS / 680,39 кг |
mt | metric ton | метрическая тонна | 0,990 MT / 0,990 мт |
n.w | netto weight | вес Нетто | N.W. / Нетто |
pcs | pieces | штука | 5 PCS / 5 шт. |
pkg, pkgs | packing | упаковка | 210 PKGS / 210 упаковок |
pl | pallet | паллет | 11 PL / 11 пал. |
qty | quantity | количество | QTY / Количество |
t | ton, tonne | тонна | 5 t / 5 т. |
Есть вопросы по морской доставке грузов? Спросите у Кредо Транс
T-SQL 2012: Оконные функции(Подробное изложение)
В данной статье мы рассмотрим работу с оконными функциями в T-SQL 2012.
Мы создадим тестовую базу с продажами горячительных напитков , и покажем на ней основные возможности оконных функций.
—Создаем базу и и добавляем тестовые данные
USE [master];
GO
IF DB_ID(‘sbase’) IS NOT NULL
DROP DATABASE sbase;
CREATE DATABASE sbase;
GO
INSERT INTO sales
VALUES (1, ‘wine’, 10, 30)
,(1, ‘beer’, 5, 15)
,(1, ‘cognac’, 4, 50)
,(2, ‘cognac’, 8, 40)
,(2, ‘vodka’, 2, 30)
,(3, ‘beer’, 15, 12)
,(3, ‘cognac’, 12, 46)
,(3, ‘vodka’, 10, 25)
,(4, ‘vodka’, 1, 30);
—С помощью PARTITION BY мы делим результат запроса на окна
—Общее количество товара и его стоимость по заказам, детально по товару
SELECT id AS N’Заказ’, product AS N’Товар’,
SUM(qty) OVER (PARTITION BY id, product) AS N’Общее кол-во в заказе’,
SUM(qty * cost) OVER (PARTITION BY id, product) AS N’Общая с-мость в заказе’,
SUM(qty * cost) OVER () AS N’Общая с-мость’
FROM sales
—С помощью ORDER BY мы можем задать сортировку
SELECT id, product, qty,
SUM(qty) OVER (PARTITION BY product) AS allQtyProduct,
SUM(qty) OVER (ORDER BY id DESC) AS allQtyId
FROM sales
SELECT id, product, qty,
SUM(qty*cost) OVER (ORDER BY id DESC RANGE CURRENT ROW) AS IdAllQtyCost
FROM sales
—Будет суммировать каждый следующий диапазон
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) AS SumQty
FROM sales
—Будет суммировать каждый следующий диапазон между указанными значениями
SELECT id, product, qty,
SUM(qty) OVER (ORDER BY id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS SumQty
FROM sales
—Пример вывода с «постраничной навигацией» используя ROW_NUMBER()
DECLARE
@pagenum AS INT = 3,
@pagesize AS INT = 2;
На этом наша статья окончена.
Для дальнейшего освоения темы рекомендую книгу: Microsoft SQL Server 2012. Высокопроизводительный код T-SQL. Оконные функции
Получение итоговых значений
Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций. Стандартом предусмотрены следующие агрегатные функции:
Функция | Описание |
COUNT(*) | Возвращает количество строк источника записей. |
COUNT( ) | Возвращает количество значений в указанном столбце. |
SUM( ) | Возвращает сумму значений в указанном столбце. |
AVG( ) | Возвращает среднее значение в указанном столбце. |
MIN( ) | Возвращает минимальное значение в указанном столбце. |
MAX( ) | Возвращает максимальное значение в указанном столбце. |
Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT( ) состоит в том, что вторая при подсчете не учитывает NULL-значения.
Пример. Найти минимальную и максимальную цену на персональные компьютеры:
SELECT MIN(price) AS Min_price, MAX(price) AS Max_price FROM PC; |
Результатом будет единственная строка, содержащая агрегатные значения:
Min_price | Max_price |
350.0 | 980.0 |
Пример. Найти имеющееся в наличии количество компьютеров, выпущенных производителем А:
SELECT COUNT(*) AS Qty FROM PC WHERE model IN (SELECT model FROM Product WHERE maker = ‘A’); |
В результате получим:
Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):
SELECT COUNT(model) AS Qty_model FROM Product WHERE maker = ‘A’; |
Совпадение результатов совершенно случайно, т.к. в базе данных количество компьютеров производителя А оказалось равным числу выпускаемых им моделей:
Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).
Для того, чтобы при получении статистических показателей использовались только уникальные значения, при аргументе агрегатных функций можно использовать параметр DISTINCT. Другой параметр ALL используется по умолчанию и предполагает подсчет всех возвращаемых значений в столбце. Оператор,
SELECT COUNT(DISTINCT model) AS Qty FROM PC WHERE model IN (SELECT model FROM Product WHERE maker = ‘A’); |
даст следующий результат:
Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY, синтаксически следующего после предложения WHERE.
Предложение GROUP BY
Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM). Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT, должны быть включены в агрегатные функции, и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY. В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После этого к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.
Если при наличии предложения GROUP BY, в предложении SELECT отсутствуют агрегатные функции, то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT, можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model; |
В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средние значения цены для каждой группы. Результатом выполнения запроса будет следующая таблица:
model | Qty_model | Avg_price |
1121 | 3 | 850.0 |
1232 | 4 | 425.0 |
1233 | 3 | 843.33333333333337 |
1260 | 1 | 350.0 |
Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).
Итак, если запрос не содержит предложения GROUP BY, то агрегатные функции, включенные в предложение SELECT, исполняются над всеми результирующими строками запроса. Если запрос содержит предложение GROUP BY, каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY, составляет группу, и агрегатные функции выполняются для каждой группы отдельно.
Предложение HAVING
Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций. Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM, а из групп таких строк. Поэтому такая проверка не может содержаться в предложении WHERE.
Заметим, что в предложении HAVING нельзя использовать псевдоним (Avg_price), используемый для именования значений агрегатной функции. Дело в том, что предложение SELECT, формирующее выходной набор запроса, выполняется предпоследним перед предложением ORDER BY.
Этот порядок не соответствует синтаксическому порядку общего формата оператора SELECT, представленному ниже:
SQL для аналитики — рейтинг прикладных задач с решениями
Привет, Хабр! У кого из вас black belt на sql-ex.ru, признавайтесь? На заре своей карьеры я немало времени провел на этом сайте, практикуясь и оттачивая навыки. Должен отметить, что это было увлекательное и вознаграждающее путешествие. Пришло время воздать должное.
В этой публикации я собрал топ прикладных задач и мои подходы к их решению в терминах SQL. Каждая задача снабжена кусочком данных и кодом, с которым можно интерактивно поиграться на SQL Fiddle.
Уверен, неискушенные пользователи смогут многое для себя почерпнуть. Продвинутых же пользователей призываю поделиться своими наиболее интересными задачами и поучаствовать в обсуждении.
Конкатенация значений из нескольких строк в одну через разделитель
Когда это может быть полезно? К примеру, если исходный набор данных хранит каждый тег, присвоенный сделке, в отдельной строке (это же получится при соединении таблиц лидов и тегов), и есть необходимость собрать все теги, обойдясь при этом без дублирования строк по каждой сделке.
Формулировка задачи: Для каждого лида вывести список тегов, разделенных запятой в одном столбце
Аналитические функции при сохранении всех строк выборки
Речь пойдет о так называемых analytic functions, которые оперируют над партициями данных (окна, windows), возвращая результат для каждой строки. В отличие от aggregate functions, “схлопывающих” строки, оконные функции оставляют все строки выборки.
Окно определяется спецификацией (выражение OVER) и основывается на трех основных концепциях:
Разбиение строк на группы (выражение PARTITION BY)
Порядок сортировки строк в каждой группе (выражение ORDER BY)
Рамки, которые определяют ограничения по количеству строк относительно каждой строки (выражение ROWS)
Таких функций существует немало, от аналитических: всем известные SUM, AVG, COUNT, менее известные LAG, LEAD, CUMEDIST, и до ранжирующих: RANK, ROWNUMBER, NTILE. Я же приведу несколько простых примеров часто встречающихся запросов:
Ко всем транзакциям пользователя вывести дату первой покупки
К каждой транзакции добавить дату предыдущей транзакции пользователя
Показать сумму покупок пользователя нарастающим итогом
Присвоить всем транзакциям пользователя / продавца / отделения порядковый номер
Работа с NULL и применение логики ветвления IF-THEN-ELSE в SQL
Про COALESCE / NVL знают все, и нет смысла останавливаться на них подробно. Зато с NVL2 и NULLIF знакомы уже не так много людей.
Как обработать ошибку деления на 0 (divide by zero error)
Как выводить NULL вместо пустых строк (‘’)
NVL2 в свою очередь вернет одно из значений, в зависимости от того, является ли входной аргумент NULL или NOT NULL. Например, если в таблице транзакций есть ссылка на invoiceid, значит транзакция в сегменте B2B, и ее следует пометить соответствующим образом.
Но больше всего мне нравится функция DECODE. Она в буквальном смысле позволяет расшифровать значения согласно заданной вами логике:
Формулировка задачи: Присвоить численному коду (или, например, битовой маске) текстовые наименования.
Дедупликация данных
Это классика. Задачу часто спрашивают на собеседованиях в формулировке “как удалить дубли / копии строк”, и решить ее можно несколькими способами. Я привык мыслить в терминах историзации данных в Хранилище, и удаление мне ни к чему, поэтому для решения задачи я воспользуюсь ранжирующей функцией ROWNUMBER().
Формулировка задачи: Выбрать самую актуальную запись с учетом статуса (успешная / отмененная транзакция) и временнОй метки
Некоторые СУБД, например, Teradata позволяют сделать запрос короче при помощи выражения QUALIFY:
Анализ временных рядов
Замечательно, если ваша BI система умеет работать с различными абсолютными и относительными фреймами, и наружу выставляет красивый визуальный интерфейс. Еще лучше, если в ваш инструментарий аналитика входит пара наиболее используемых функций:
Конвертация Unix Timestamp (epoch) в человекочитаемый формат
Анализ истории со Slowly Changing Dimensions (SCD)
Формулировка задачи: Какой статус был у клиентов на 3-й день месяца?
Формулировка задачи: Как в течение недели росло количество активных клиентов?
С помощью такого подхода можно подсчитать долю неактивных контрагентов на каждую дату за последний месяц. При этом неактивным считается контрагент, не совершивший ни одной транзакции за предыдущие 7 дней на каждую дату. Вот так может выглядеть визуализация решения задачи на дашборде:
Использование выражения CASE в агрегирующих функциях
Агрегирующие функции могут принимать в качестве аргумента результат оценки выражения CASE. Таким образом можно к агрегируемым строкам применить псевдофильтр. Это напоминает мне использование формулы СУММЕСЛИ из старого доброго Excel, только для реляционных баз данных. Смотрите сами:
Подсчитать все лиды и выручку
Подсчитать количество лидов со статусом success
Подсчитать выручку лидов с тегом python
Парсинг колонки с разделением на отдельные атрибуты
Чаще всего так поступают в условиях внешних ограничений, когда иного выхода нет. Например, при ограниченном наборе полей в CRM системе. Или при передаче нескольких UTM-меток в одной строковой переменной. Еще так могут делать люди, которые не слышали про нормализацию данных.
Формулировка задачи: Выделить закодированные в названии кампании атрибуты в отдельные колонки: сеть, регион, категория, температура, бренд.
Чуть более сложная ситуация с парсингом UTM-меток, а именно UTMContent, которая по сути является контейнером для произвольного набора атрибутов, разделенных любым символом. Поэтому стоит быть последовательным и аккуратным при формировании таких меток, хотя зачастую инженер вынужден работать с тем, что есть.
Формулировка задачи: Разбить строку UTMContent на отдельные атрибуты cid, gid, aid, kwd с соблюдением соответствия ключ-значение. Каждое значение предваряется наименованием ключа, все значения разделены вертикальной строкой (|).
FULL JOIN для соединений без потери строк
Уверен, что все знают про FULL JOIN, но кто хоть иногда использует этот тип соединения? Это незаменимый подход в ситуациях, когда я хочу сохранить все исходные строки с каждой стороны джоина. Иначе говоря, недопустимо терять факты трат денежных средств, даже если для них не нашлось соответствующих лидов в таблицах CRM.
Формулировка задачи: Подготовить витрину-трекер для сквозной аналитики лидов из CRM и трат из Рекламных Кабинетов (Яндекс.Директ, Google Adwords, Facebook).
Пример упрощен и умозрителен. Однако этой задаче я посвятил одну из своих предыдущих публикаций: Сквозная Аналитика на Azure SQL + dbt + Github Actions + Metabase и недавнее выступление на вебинаре: Путь Инженера Аналитики: Решение для Маркетинга. Тема заслуживает отдельного внимания.
Разбиение пользовательских событий на сессии
Для чего это можно использовать? Прежде всего, для того, чтобы перейти от анализа хитов (кликов) к полноценному анализу пользовательского взаимодействия и поведения. Во-вторых, улучшение UX и качества сервисов, проведение A/B тестирования. Наконец, поиск паттернов, определенных сегментов пользователей, в том числе fraud monitoring (защита от мошенничества и ботов).
30 минут бездействия
Начало новых суток
Смена источника трафика (возврат на сайт по клику на новый рекламный баннер)
Базовая задача сессионизации сводится к следующему: превратить последовательность кликов из лога веб-сервера в набор сессий.
Попробуем декомпозировать и решить задачу по частям:
Шаг 1. Для каждого пользователя берем идентификатор просмотра, время просмотра, источник трафика (хеш-сумма). Хеш-сумма берется от текстовой конкатенации атрибутов источника трафика: utm_source + utm_medium + utm_campaign. При этом обрабатываются null-значения в любом из столбцов (заменяются на литерал ‘null’). По хеш-сумме легко проверить смену источника трафика.
Шаг 3. Рассчитываем, является ли каждый хит началом новой сессии. Это проверка на выполнение любого из трех указанных выше условий окончания сессии:
Шаг 4. Присваиваем каждой сессии уникальный идентификатор. Для этого сначала необходимо пронумеровать сессии одного пользователя монотонно возрастающими числами. Затем построить уникальный суррогатный ключ сессии: к номеру сессии добавить идентификатор пользователя, взять хеш-сумму:
В реальном мире всё сложнее
Помимо логики, выраженной в SQL, не меньшее значение имеет ряд других факторов:
СУБД, с которой вы работаете: то, какие функции и возможности она поддерживает, формат хранения данных: в виде колонок или строк
Фактически используемый план выполнения запроса: алгоритмы соединения таблиц, локальность операций, наличие статистических данных у оптимизатора
Используемые физические и логические модели данных: индексы, материализованные представления, кеш, предварительно отсортированные данные
На занятиях курса Data Engineer я и мои коллеги готовим объемлющий и интересный контент, затрагивающий множество тем, связанных с архитектурой аналитических приложений, внутренним устройством систем обработки больших данных и развертыванием ML.
Советую посетить ближайшие открытые вебинары:
Оставляйте ваши комментарии и вопросы, предлагайте собственные примеры задач и подходы к решению.