Стихи

Материал из Home Wiki
Перейти к: навигация, поиск

                                                                                                           4.

Вывести кол-во записей в справочнике товаров, удовлетворяющих следующим условиям:

Вес единицы товара кратен 1 кг и находится в диапазоне от 1 до 3х кг

Объем единицы товара в диапазоне от 3х до 5и литров

SELECT Count(*) AS Cnt

FROM ANP.T_SQLT_ART

WHERE 1 = 1

AND WEIGTH IN (1,2,3)

AND VOL_TRANSP BETWEEN 3 AND 5

Ответ: 9437

5.

Вывести средний/минимальный/максимальный вес товара для каждой ГР21

Детализация на выходе: ГР20-ГР21-Средний вес товара-Минимальный вес товара-Максимальнй вес товара

Данные отсортировать по возрастанию полей ГР20-ГР21

Результаты округлить до 2х знаков после запятой

SELECT

ART_GRP_LVL_0_NAME

,ART_GRP_LVL_1_NAME

,Round(Avg(WEIGTH),2) AS avgW

,Round(Min(WEIGTH),2) AS minW

,Round(Max(WEIGTH),2) AS maxW

FROM ANP.T_SQLT_ART

GROUP BY

ART_GRP_LVL_0_NAME

,ART_GRP_LVL_1_NAME

ORDER BY

ART_GRP_LVL_0_NAME

,ART_GRP_LVL_1_NAME

На выходе 262 строки

6.

Вывести список товаров из ГР22 - Сливки, которые поместятся в мешок объемом 1 литр

Для позиций из списка нужно расчитать сколько целых товаров поместится в этот мешок

Детализация на выходе: Название товара-Сколько целых товаров поместится в этот мешок

Данные отсортировать по возрастанию поля Название товара

SELECT

NAME

,Cast((1/VOL_TRANSP) AS INTEGER) AS Cnt

FROM ANP.T_SQLT_ART

WHERE VOL_TRANSP <= 1

AND ART_GRP_LVL_2_NAME = 'Сливки'

ORDER BY NAME

На выходе 618 строк

7.

Определить среднюю плотность(кг/л) товаров из справочника товаров

Результат округлить до 2х знаков после запятой

SELECT Round(Avg(WEIGTH/VOL_TRANSP),2) AS a

FROM ANP.T_SQLT_ART t

Ответ: 1,51

8.

Выведите средний/минимальный/максимальный объём товаров из справочника товаров тремя разными строчками

Данные отсортировать по убыванию полученных значений объемов

Результаты округлить до 8и знаков после запятой

SELECT Round(vol,8) AS vol

FROM (

SELECT Max(VOL_TRANSP) AS vol

FROM ANP.T_SQLT_ART

UNION ALL

SELECT Avg(VOL_TRANSP) AS vol

FROM ANP.T_SQLT_ART

UNION ALL

SELECT Min(VOL_TRANSP) AS vol

FROM ANP.T_SQLT_ART

) t

ORDER BY vol DESC

Ответ:

20788450,58000000

80,12363904

0,00006100

9.

Выведите 5 товаров, набравших наибольшую суммарную выручку(ANP.T_SQLT_SALES.SALE) за 2018 год

Детализация на выходе: Название товара-ГР20-Суммарная выручка, руб

Данные отсортировать по убыванию значения суммарной выручки

Вариант 1

SELECT

a.NAME

,a.ART_GRP_LVL_0_NAME

,s.vol

FROM (

SELECT TOP 5

ART_ID

,Sum(s.SALE) vol

FROM ANP.T_SQLT_SALES s

WHERE DAY_ID BETWEEN '2018-01-01' AND '2018-12-31'

GROUP BY ART_ID

ORDER BY vol DESC

) s

JOIN ANP.T_SQLT_ART a

ON s.ART_ID = a.ART_ID

ORDER BY vol DESC

Вариант 2

SELECT TOP 5

a.NAME

,a.ART_GRP_LVL_0_NAME

,s.vol

FROM (

SELECT

ART_ID

,Sum(s.SALE) vol

FROM ANP.T_SQLT_SALES s

WHERE DAY_ID BETWEEN '2018-01-01' AND '2018-12-31'

GROUP BY ART_ID

) s

JOIN ANP.T_SQLT_ART a

ON s.ART_ID = a.ART_ID

ORDER BY vol DESC

Ответ:

БАНАНЫ 1кг    Свежие фрукты    10504171,855100

ПФ К/к Окорок свиной б/к охл (СП ГМ)    Мясо    6935712,174000

Цыпленок бройлер охл. 1 кат. Поли/уп 1кг :13    Птица    6121238,022100

МАЙКОПСКОЕ Пиво Честное непаст (в) (Майкопский ПЗ)    Слабоалкогольные напитки    3932186,796700

МАНДАРИНЫ 1кг    Свежие фрукты    3931686,867000

10.

Выведите 5 ГР21, имеющих наибольшую доходность(Выручка, руб - Себестоимость, руб) за весь период

Для полученных групп выведите остатки в рублях на 30 июня 2019 г. и 30 июня 2018 г.

Детализация на выходе: ГР20-ГР21-Доходность, руб-Остатки, руб на 30 июня 2019 г.-Остатки, руб на 30 июня 2018 г.

Данные отсортировать по убыванию значения доходности

SELECT *

FROM (

SELECT

s.ART_GRP_LVL_0_NAME

,s.ART_GRP_LVL_1_NAME

,Avg(s.vol) AS vol

,Sum(CASE WHEN DAY_ID = '2019-06-30' THEN REST_CP ELSE 0 END) AS REST_CP_2019

,Sum(CASE WHEN DAY_ID = '2018-06-30' THEN REST_CP ELSE 0 END) AS REST_CP_2018

FROM (

SELECT TOP 5

a.ART_GRP_LVL_0_NAME

,a.ART_GRP_LVL_1_NAME

,Sum(SALE - SALE_COGS) AS vol

FROM ANP.T_SQLT_SALES s

JOIN ANP.T_SQLT_ART a

ON s.ART_ID = a.ART_ID

GROUP BY a.ART_GRP_LVL_0_NAME, a.ART_GRP_LVL_1_NAME

ORDER BY vol DESC

) s

JOIN (

SELECT

DAY_ID

,a.ART_GRP_LVL_0_NAME

,a.ART_GRP_LVL_1_NAME

,Sum(REST_CP) AS REST_CP

FROM ANP.T_SQLT_REST s

JOIN ANP.T_SQLT_ART a

ON s.ART_ID = a.ART_ID

WHERE DAY_ID IN ('2019-06-30','2018-06-30')

GROUP BY DAY_ID, a.ART_GRP_LVL_0_NAME, a.ART_GRP_LVL_1_NAME

) r

ON s.ART_GRP_LVL_0_NAME = r.ART_GRP_LVL_0_NAME

AND s.ART_GRP_LVL_1_NAME = r.ART_GRP_LVL_1_NAME

GROUP BY s.ART_GRP_LVL_0_NAME, s.ART_GRP_LVL_1_NAME

) t

ORDER BY vol DESC

Ответ:

Кулинария    Готовые блюда СП    28779176,69    150,427045    0,000000

Кулинария    Салаты СП    23951628,57    0,165496    0,000000

Хлеб и хлебобулочные изделия    Хлеб и хлебобулочные изделия (выпечка)    23422171,42    9364,606241    15005,509098

Мясная гастрономия    Колбасы    18492596,46    498434,908481    527739,158574

Кондитерские изделия    Современные кондитерские изделия    17844440,65    785533,103561    779386,802228

11.

Определите "проникновение" чеков каждой ГР21 (чеков в группе/чеков всего) за 1 января 2019 года

Детализация на выходе: ГР20-ГР21-Чеков в группе-Чеков всего-Проникновение, доля

Данные отсортировать по убыванию полей Проникновение, доля-ГР21

Точность проникновения - 8 знаков после запятой

SELECT

a.ART_GRP_LVL_0_NAME

,a.ART_GRP_LVL_1_NAME

,a.Cnt

,d.cntALL

,Cast(a.Cnt AS DECIMAL(18,8))/d.cntALL AS p

FROM (

SELECT

a.ART_GRP_LVL_0_NAME

,a.ART_GRP_LVL_1_NAME

,Count(*) Cnt

FROM (

SELECT DISTINCT

a.ART_GRP_LVL_0_NAME

,a.ART_GRP_LVL_1_NAME

,TXN_ID

FROM ANP.T_SQLT_TXN t

JOIN ANP.T_SQLT_ART a

ON t.ART_ID = a.ART_ID

WHERE DAY_ID BETWEEN '2019-01-01' AND '2019-01-01'

) a

GROUP BY

a.ART_GRP_LVL_0_NAME

,a.ART_GRP_LVL_1_NAME

) a

CROSS JOIN (

SELECT Count(*) AS cntALL

FROM (

SELECT DISTINCT

TXN_ID

FROM ANP.T_SQLT_TXN

WHERE DAY_ID BETWEEN '2019-01-01' AND '2019-01-01'

) d

) d

ORDER BY p DESC, ART_GRP_LVL_1_NAME DESC

На выходе 164 строки

12.

Выведите 5 товаров, наиболее часто встречающихся в одном чеке вместе с товарами ГР21 - Пиво

При этом товары ГР21 - Пиво и ГР21 - Пакеты не должны попасть в эти 5 товаров

Детализация на выходе: Название товара-Выручка, руб-Сколько раз товар встречен с чеками группы пиво-В скольки чеках товар встречался впринципе

Данные отсортировать по убыванию значения выручки

SELECT

NAME

,Cnt

,cntAll

,vol

FROM (

SELECT

r.ART_ID

,Max(r.Cnt) AS Cnt

,Max(r.cntAll) AS cntAll

,Sum(s.SALE) vol

FROM (

SELECT

r.ART_ID

,Max(r.Cnt) AS Cnt

,Count(*)     AS cntAll

FROM (

SELECT TOP 5

t.ART_ID

,Count(*) AS Cnt

FROM ANP.T_SQLT_TXN t

JOIN ANP.T_SQLT_ART a

ON t.ART_ID = a.ART_ID

WHERE TXN_ID IN (    SELECT

TXN_ID

FROM ANP.T_SQLT_TXN t

JOIN ANP.T_SQLT_ART a

ON t.ART_ID = a.ART_ID

WHERE ART_GRP_LVL_1_NAME = 'Пиво')

AND ART_GRP_LVL_1_NAME NOT IN ('Пиво','Пакеты')

GROUP BY t.ART_ID

ORDER BY Cnt DESC

) r

JOIN ANP.T_SQLT_TXN t

ON t.ART_ID = r.ART_ID

GROUP BY r.ART_ID

) r

JOIN ANP.T_SQLT_SALES s

ON s.ART_ID = r.ART_ID

GROUP BY r.ART_ID

) r

JOIN ANP.T_SQLT_ART a

ON r.ART_ID = a.ART_ID

ORDER BY vol DESC

Ответ:

БАНАНЫ 1кг    26849    493867    28457788,202100

Сахар белый кристаллический фас 1кг    10910    121133    8440916,500000

ЛИМОНЫ 1кг    11311    129385    4247990,803800

ЛУК репчатый 1кг    17321    171810    4102646,771900

Батон Летний нарез в/с 0,35кг п/уп(Хлебозавод№6)    10451    96097    3312037,400000

13.

Определите ТОП 5 самых продаваемых товаров за весь период по выручке, руб

Посчитайте суммарную доходность (Выручка, руб - Себестоимость, руб) этих товаров

Определите кол-во чеков, содержащих ТОП 2 из этих товаров, но не содержащий оставшиеся 3 товара

Детализация на выходе: Доходность, руб-Кол-во чеков

SELECT s.vol, t.Cnt

FROM

(

SELECT

Sum(vol1 - vol2) AS vol

FROM (

SELECT TOP 5

ART_ID

,Sum(s.SALE)         AS vol1

,Sum(s.SALE_COGS)    AS vol2

FROM ANP.T_SQLT_SALES s

GROUP BY ART_ID

ORDER BY vol1 DESC

) s

) s

CROSS JOIN (

SELECT

Count(*) AS Cnt

FROM (

SELECT DISTINCT

TXN_ID

FROM ANP.T_SQLT_TXN t

WHERE ART_ID IN (    SELECT ART_ID

FROM (

SELECT

ART_ID

,Sum(s.SALE) vol

FROM ANP.T_SQLT_SALES s

GROUP BY ART_ID

QUALIFY Row_Number() Over (ORDER BY vol DESC) IN (1,2)

) t

)

AND TXN_ID NOT IN (    SELECT

TXN_ID

FROM ANP.T_SQLT_TXN t

WHERE ART_ID IN (    SELECT ART_ID

FROM (

SELECT

ART_ID

,Sum(s.SALE) vol

FROM ANP.T_SQLT_SALES s

GROUP BY ART_ID

QUALIFY Row_Number() Over (ORDER BY vol DESC) IN (3,4,5)

) t

)

)

) t

) t

Ответ:

1074000,853268    507750

14.

Определите самый популярный чек (состав чека) за январь 2019 года

На выходе необходим список названий товаров, содержащихся в этом чеке

Если популярных чеков несколько, то выбрать чек с наибольшей суммарной выручкой за всеь период

Данные отсортировать по возрастанию поля Название товара

SELECT a.NAME

FROM (

SELECT TOP 1 r.*

FROM (

SELECT r.*

,Count(*) Over (PARTITION BY lvl) lvl_Cnt

FROM (

SELECT TXN_ID

,Count(*)     AS _Cnt

,Avg(ART_ID) AS _Avg

,Min(ART_ID) AS _Min

,Max(ART_ID) AS _Max

,Sum(ART_ID) AS _Sum

,Rank() Over (ORDER BY _Cnt, _Avg, _Min, _Max, _Sum) AS lvl

FROM ANP.T_SQLT_TXN

WHERE DAY_ID BETWEEN '2019-01-01' AND '2019-12-31'

GROUP BY TXN_ID

) r

) r

/*QUALIFY Max(lvl_Cnt) Over() = lvl_Cnt*/

ORDER BY lvl_Cnt DESC

) r

JOIN ANP.T_SQLT_TXN t

ON r.TXN_ID = t.TXN_ID

JOIN ANP.T_SQLT_ART a

ON a.ART_ID = t.ART_ID

ORDER BY NAME

Ответ:

БАНАНЫ 1кг

15.

Провести ABC анализ, который позволит классифицировать товары по степени их важности в доле продаж

Необходимо распределить товары внутри ГР22 по категорииям ABC, используя накопительную сумму рублевых продаж

Наиболее продаваемые товары попадут в категорию "A", наименее - "C"

Категория C до 20% накопительной суммы* (не включительно)

Категория B до 50% накопительной суммы* (не включительно)

Категория A до 100% накопительной суммы*

* Накопительную сумму считаем в порядке возрастания продаж

После получения для каждого уникального товара его категории внутри ГР22, рассчитаем сколько в итоге товаров в каждой категории

Результат должен содержать 3 строки в разрезе ABC-Количество товаров, отсортированных по категориям ABC в порядке возрастания

SELECT ABC, Count(*) AS cntABC

FROM (

SELECT

Sum(s.SALE) Over (PARTITION BY s.ART_GRP_LVL_2_ID ORDER BY s.SALE ASC ROWS BETWEEN Unbounded Preceding AND CURRENT ROW) AS artSum

,Sum(s.SALE) Over (PARTITION BY s.ART_GRP_LVL_2_ID) AS allSum

,ZeroIfNull(artSum / NullIfZero(allSum)) AS itog

,CASE

WHEN itog < 0.2 THEN 'C'

WHEN itog < 0.5 THEN 'B'

ELSE 'A'

END AS ABC

FROM (

SELECT

a.ART_GRP_LVL_2_ID

,s.ART_ID

,Sum(s.SALE) AS SALE

FROM ANP.T_SQLT_SALES s

JOIN T_SQLT_ART a

ON s.ART_ID = a.ART_ID

GROUP BY ART_GRP_LVL_2_ID, s.ART_ID

) AS s

) s

GROUP BY ABC

ORDER BY ABC

Ответ:

A    5531

B    7813

C    27764