Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Задачки на собеседовании  [new]
PavluxaF
Member

Откуда: СПб
Сообщений: 126
Доброго времени суток!
Выкладываю задачки с собеседования и решения. Помогите решить/оптимизировать

Задача
В базе источнике есть таблица с данными о продажах.
Продажи (Период, Магазин, НомерЧека, Продавец, Товар, Количество, Сумма)
Уникальный идентификатор одного чека продажи – это набор полей [Период, Магазин, НомерЧека] . Таблица содержит порядка 1 млн записей в день. Данные с 2004 года. Данные могут меняться задним числом, за последние 2 месяца. Данные попадают в таблицу источник с 16.00 до 7.00 (следующего дня).

Напишите скрипт создания таблицы в DWH для хранения этой информации.

Таблица будет использоваться для куба Продажи и для выбора информации по запросам пользователей

Как будет выглядеть etl процесс заливки данных


Задача2
Есть таблица, которая содержит данные об учёте рабочего времени сотрудников:
ПриходУходСотрудников
сотрудникID
ДатаВремя
магазинID
ПриходУход (1 - зашел; -1 - вышел )

Нужно написать запрос, на основе которого будет формироваться отчёт “Количество сотрудников в магазинах по часам за сегодня"


Решение2
DECLARE @EMPLYEE TABLE
(
[E_ID] INT,
[DT] DATETIME,
[OFFICE_ID] INT,
[IO] BIT
)

INSERT INTO @EMPLYEE([E_ID],[DT],[OFFICE_ID],[IO])
SELECT 1,'20141201 08:00:00',1,1 UNION ALL
SELECT 1,'20141201 10:00:00',1,0 UNION ALL
SELECT 1,'20141201 12:00:00',1,1 UNION ALL
SELECT 1,'20141201 17:00:00',1,0 UNION ALL
SELECT 2,'20141201 08:00:00',1,1 UNION ALL
SELECT 2,'20141201 10:00:00',1,0 UNION ALL
SELECT 3,'20141201 10:00:00',1,1 UNION ALL
SELECT 3,'20141201 17:00:00',1,0 UNION ALL
SELECT 3,'20141101 10:00:00',1,1 UNION ALL
SELECT 3,'20141101 17:00:00',1,0 UNION ALL
SELECT 4,'20141201 09:00:00',1,1 UNION ALL
SELECT 4,'20141201 17:00:00',1,0

--кто-то зашел и еще не выходил
INSERT INTO @EMPLYEE([E_ID],[DT],[OFFICE_ID],[IO])
SELECT 5,'20141201 10:00:00',1,1

----время в минутах
INSERT INTO @EMPLYEE([E_ID],[DT],[OFFICE_ID],[IO])
SELECT 6,'20141201 10:05:00',1,1 UNION ALL
SELECT 6,'20141201 16:59:00',1,0

BEGIN
WITH CTE(HOUR_PART) AS
(
SELECT '01:00:00' UNION ALL SELECT '02:00:00' UNION ALL SELECT '03:00:00' UNION ALL SELECT '04:00:00' UNION ALL SELECT '05:00:00' UNION ALL SELECT '06:00:00' UNION ALL
SELECT '07:00:00' UNION ALL SELECT '08:00:00' UNION ALL SELECT '09:00:00' UNION ALL SELECT '10:00:00' UNION ALL SELECT '11:00:00' UNION ALL SELECT '12:00:00' UNION ALL
SELECT '13:00:00' UNION ALL SELECT '14:00:00' UNION ALL SELECT '15:00:00' UNION ALL SELECT '16:00:00' UNION ALL SELECT '17:00:00' UNION ALL SELECT '18:00:00' UNION ALL
SELECT '19:00:00' UNION ALL SELECT '20:00:00' UNION ALL SELECT '21:00:00' UNION ALL SELECT '22:00:00' UNION ALL SELECT '23:00:00' UNION ALL SELECT '00:00:00'
)

SELECT CTE.HOUR_PART,COUNT(T.[E_ID])
FROM
(
--вошел и вышел
SELECT E2.[E_ID],CAST(E1.[DT] AS TIME) AS [H_FROM],CAST(MIN(E2.[DT]) AS TIME) AS [H_TO]
FROM @EMPLYEE E1
INNER JOIN @EMPLYEE E2 ON E2.[IO] = 0 AND E1.[E_ID] = E2.[E_ID] AND E2.[DT] > E1.[DT]
WHERE E1.[IO] = 1 AND E1.[DT] > CAST(GETDATE() AS DATE) --ЕСЛИ ЗА СЕГОДНЯ (СЮДА МОЖНО ВСТАВИТЬ ЛЮБОЙ BETWEEN ПО ЧИСЛАМ)
GROUP BY E2.[E_ID],E1.[DT]

UNION ALL

--те кто вошел и еще не выходил (считаем что он до сих пор на работе)
SELECT E1.[E_ID],CAST(E1.[DT] AS TIME),CAST(GETDATE() AS TIME)
FROM @EMPLYEE E1
LEFT JOIN @EMPLYEE E2 ON E2.[IO] = 0 AND E1.[E_ID] = E2.[E_ID] AND E2.[DT] > E1.[DT]
WHERE E1.[IO] = 1 AND E2.[DT] IS NULL AND E1.[DT] > CAST(GETDATE() AS DATE) --ЕСЛИ ЗА СЕГОДНЯ (СЮДА МОЖНО ВСТАВИТЬ ЛЮБОЙ BETWEEN ПО ЧИСЛАМ)
) T
RIGHT JOIN CTE ON CTE.[HOUR_PART] BETWEEN T.[H_FROM] AND T.[H_TO]
GROUP BY CTE.HOUR_PART
END


Задача3
Вопрос 2

На основании таблицы Продажи из блока DWH + etl напишите запрос, который возвращает полную сумму чеков в разрезе по месяцам, в которых

одновременно был продан Товар1 и Товар2

был продан Товар1 без Товар2

был продан Товар2 без Товар1

ни Товар1, ни Товар2


Решение3
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @SALES TABLE
(
[PERIOD] [datetime] NOT NULL,
[OFFICE] [nvarchar](255) NOT NULL,
[TICKET] [bigint] NOT NULL,
[SELLER] [nvarchar](255) NOT NULL,
[GOOD] [nvarchar](255) NOT NULL,
[COUNT] [int] NOT NULL,
[SUM] [money] NOT NULL
)

INSERT INTO @SALES([PERIOD],[OFFICE],[TICKET],[SELLER],[GOOD],[COUNT],[SUM])
SELECT '20141201 00:00:00',1,1,'Иван','А',1,123 UNION ALL
SELECT '20141201 00:00:00',1,1,'Иван','Б',1,234 UNION ALL
SELECT '20141202 00:00:00',1,2,'Иван','А',1,123 UNION ALL
SELECT '20141203 00:00:00',1,3,'Иван','Б',1,234 UNION ALL
SELECT '20141204 00:00:00',2,5,'Иван','А',1,123 UNION ALL
SELECT '20141204 00:00:00',2,5,'Иван','Б',1,234 UNION ALL
SELECT '20141205 00:00:00',1,3,'Иван','Б',1,234 UNION ALL
SELECT '20141205 00:00:00',1,5,'Иван','А',1,123 UNION ALL

SELECT '20141101 00:00:00',7,1,'Иван','А',1,456 UNION ALL
SELECT '20141101 00:00:00',7,1,'Иван','Б',1,567

--1)
SELECT DATEPART(MONTH,S3.[PERIOD]),SUM(S3.[SUM])
FROM
(
SELECT DISTINCT S1.[PERIOD],S1.[OFFICE],S1.[TICKET]
FROM @SALES S1
INNER JOIN @SALES S2 ON S1.[PERIOD] = S2.[PERIOD] AND S1.[OFFICE] = S2.[OFFICE] AND S1.[TICKET] = S2.[TICKET] AND S2.[GOOD] = 'Б'
WHERE S1.[GOOD] = 'А'
) T
INNER JOIN @SALES S3 ON S3.[PERIOD] = T.[PERIOD] AND S3.[OFFICE] = T.[OFFICE] AND S3.[TICKET] = T.[TICKET]
GROUP BY DATEPART(MONTH,S3.[PERIOD])

--2)
SELECT DATEPART(MONTH,S3.[PERIOD]),SUM(S3.[SUM])
FROM
(
SELECT DISTINCT S1.[PERIOD],S1.[OFFICE],S1.[TICKET]
FROM @SALES S1
LEFT JOIN @SALES S2 ON S1.[PERIOD] = S2.[PERIOD] AND S1.[OFFICE] = S2.[OFFICE] AND S1.[TICKET] = S2.[TICKET] AND S2.[GOOD] = 'Б'
WHERE S1.[GOOD] = 'А' AND S2.[PERIOD] IS NULL
) T
INNER JOIN @SALES S3 ON S3.[PERIOD] = T.[PERIOD] AND S3.[OFFICE] = T.[OFFICE] AND S3.[TICKET] = T.[TICKET]
GROUP BY DATEPART(MONTH,S3.[PERIOD])

--3)
SELECT DATEPART(MONTH,S3.[PERIOD]),SUM(S3.[SUM])
FROM
(
SELECT DISTINCT S1.[PERIOD],S1.[OFFICE],S1.[TICKET]
FROM @SALES S1
LEFT JOIN @SALES S2 ON S1.[PERIOD] = S2.[PERIOD] AND S1.[OFFICE] = S2.[OFFICE] AND S1.[TICKET] = S2.[TICKET] AND S2.[GOOD] = 'А'
WHERE S1.[GOOD] = 'Б' AND S2.[PERIOD] IS NULL
) T
INNER JOIN @SALES S3 ON S3.[PERIOD] = T.[PERIOD] AND S3.[OFFICE] = T.[OFFICE] AND S3.[TICKET] = T.[TICKET]
GROUP BY DATEPART(MONTH,S3.[PERIOD])

--4)
SELECT DATEPART(MONTH,S3.[PERIOD]),SUM(S3.[SUM])
FROM
(
SELECT DISTINCT S1.[PERIOD],S1.[OFFICE],S1.[TICKET]
FROM @SALES S1
WHERE S1.[GOOD] NOT IN ('А','Б') AND NOT EXISTS
(
SELECT TOP 1 1
FROM @SALES S2
WHERE S1.[PERIOD] = S2.[PERIOD] AND S1.[OFFICE] = S2.[OFFICE] AND S1.[TICKET] = S2.[TICKET] AND S2.[GOOD] IN ('А','Б')
)
) T
INNER JOIN @SALES S3 ON S3.[PERIOD] = T.[PERIOD] AND S3.[OFFICE] = T.[OFFICE] AND S3.[TICKET] = T.[TICKET]
GROUP BY DATEPART(MONTH,S3.[PERIOD])
1 дек 14, 18:00    [16933118]     Ответить | Цитировать Сообщить модератору
 Re: Задачки на собеседовании  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
PavluxaF,

А зарплату можно будет за вас получать?
2 дек 14, 00:21    [16934670]     Ответить | Цитировать Сообщить модератору
 Re: Задачки на собеседовании  [new]
aleks2
Guest
Задача2
DECLARE @EMPLYEE TABLE(
[E_ID] INT,
[DT] DATETIME,
[OFFICE_ID] INT,
[IO] BIT
)

INSERT INTO @EMPLYEE([E_ID],[DT],[OFFICE_ID],[IO])
SELECT 1,'20141201 08:00:00',1,1 UNION ALL
SELECT 1,'20141201 10:00:00',1,0 UNION ALL
SELECT 1,'20141201 12:00:00',1,1 UNION ALL
SELECT 1,'20141201 17:00:00',1,0 UNION ALL
SELECT 2,'20141201 08:00:00',1,1 UNION ALL
SELECT 2,'20141201 10:00:00',1,0 UNION ALL
SELECT 3,'20141201 10:00:00',1,1 UNION ALL
SELECT 3,'20141201 17:00:00',1,0 UNION ALL
SELECT 3,'20141101 10:00:00',1,1 UNION ALL
SELECT 3,'20141101 17:00:00',1,0 UNION ALL
SELECT 4,'20141201 09:00:00',1,1 UNION ALL
SELECT 4,'20141201 17:00:00',1,0

INSERT INTO @EMPLYEE([E_ID],[DT],[OFFICE_ID],[IO])
SELECT 5,'20141201 10:00:00',1,1

INSERT INTO @EMPLYEE([E_ID],[DT],[OFFICE_ID],[IO])
SELECT 6,'20141201 10:05:00',1,1 UNION ALL
SELECT 6,'20141201 16:59:00',1,0

declare @now datetime;
set @now = '20141201';

declare @hrs table(h tinyint primary key clustered);
declare @n tinyint
set @n = 24;
while @n>0 begin set @n = @n - 1; insert @hrs values(@n); end;

with
  x as  ( select e.OFFICE_ID, e.DT, SUM( case e1.IO when 1 then 1 else -1 end ) n 
            from @EMPLYEE e inner join @EMPLYEE e1 on e.OFFICE_ID = e1.OFFICE_ID and e.DT >= e1.DT group by e.OFFICE_ID, e.DT 
        )
, y as  ( select *, row_number() over( partition by OFFICE_ID order by DT ) nn from x )
, z as  ( select y.OFFICE_ID, y.DT as DT0, y1.DT as DT1, y.n from y inner join y y1 on y.OFFICE_ID = y1.OFFICE_ID and y.nn + 1 = y1.nn )
, h as  ( select dateadd(hour, h, @now) h0, dateadd(hour, h + 1, @now) h1 from @hrs )
, hz as ( select z.OFFICE_ID, h.h0, z.n
               , case when h0 < DT0 then DT0 else h0 end as DT0 , case when h1 > DT1 then DT1 else h1 end as DT1
           from h inner join z on h.h1 > z.DT0 and z.DT1 > h.h0)
select OFFICE_ID, h0 as hour, sum( n * datediff(minute, DT0, DT1) )/60. as n  from hz group by OFFICE_ID, h0;
2 дек 14, 06:13    [16934987]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить