Форум программистов, компьютерный форум, киберфорум
Наши страницы
Microsoft SQL Server
Войти
Регистрация
Восстановить пароль
 
Рейтинг 4.50/6: Рейтинг темы: голосов - 6, средняя оценка - 4.50
naHuka
46 / 33 / 14
Регистрация: 12.09.2013
Сообщений: 167
1

Посчитать размер клиентской за промежуток в год на дату

12.07.2019, 00:22. Просмотров 1062. Ответов 13

Доброго времени суток, Камрадс! Раз интересных топиков нет сегодня ночью, попробую предложить к обсуждению такой кейс:


Таблица cdm.loan (обрезанная) - сущность события

SQL
1
2
3
4
5
6
7
8
9
10
CREATE TABLE [CDM].[loan](
    [loan_key] [INT] NOT NULL, --уникальный идентификатор
    [start_dt] [DATE] NULL,       --дата события
    [client_key] [INT] NULL,       --ключ клиента
    [trader_nm] [nvarchar](255) NULL, --наименование продукта
 CONSTRAINT [pk_cdm_loan] PRIMARY KEY CLUSTERED 
(
    [loan_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Таблица cdm.client_risk_hist (обрезанная) - сущность значение риск грейда клиента на дату

SQL
1
2
3
4
5
6
CREATE TABLE [CDM].[client_risk_hist](
    [client_key] [INT] NOT NULL,
    [risk_grade] [nvarchar](5) NULL,
    [effective_from_dttm] [datetime] NULL,
    [effective_to_dttm] [datetime] NULL,
) ON [PRIMARY]
Суть:

Есть итоговая таблица содержащее большое количество столбцов.
Дата в столбце представлена в виде конца месяца, т.е. информация в этой итоговой таблице предоставляется на эту дату, назовём эту дату контрольной.
Необходимо посчитать количество уникальных клиентов, совершивших покупку по каждому продукту 1 продукт = 1 столбец) в разрезе риск грейдов за период dateadd(yy,-1,КонтрольнаяДата) и Контрольная дата.


Для решения был предложен следующий sql скрипт:

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP TABLE IF EXISTS #eo
SELECT DISTINCT CAST(c.MonthEnd AS DATE) AS [EOMONTH]
INTO #eo
FROM dbo.Calendar AS c
;
 
DROP TABLE IF EXISTS #tmp2;
SELECT e.[EOMONTH],l.trader_nm,crh.risk_grade, COUNT(DISTINCT l.client_key) cnt_client
INTO #tmp2
FROM #eo e 
LEFT JOIN cdm.loan AS l ON e.[EOMONTH] BETWEEN DATEADD(yy,-1,l.start_dt) AND l.start_dt
LEFT JOIN cdm.client_risk_hist AS crh ON crh.client_key = l.client_key AND l.start_dt BETWEEN crh.effective_from_dttm AND crh.effective_to_dttm
WHERE l.trader_nm IN(N'Продукт 1', N'Продукт 2', N'Продукт 3' , N'Продукт 3')
GROUP BY e.[EOMONTH],l.trader_nm,crh.risk_grade
ORDER BY 1,2,3
Но вот ведь неладное, проверочный скрипт возвращает какую-то ахинею:

SQL
1
2
3
4
5
SELECT EOMONTH, trader_nm, SUM(cnt_client)
 FROM #tmp2
WHERE trader_nm LIKE N'Продукт 1'
GROUP BY EOMONTH, trader_nm
ORDER BY EOMONTH
Кликните здесь для просмотра всего текста
Снимок.png во вложении


Но если проверить реальные данные, то можно увидеть

SQL
1
SELECT COUNT(DISTINCT l.client_key) FROM cdm.loan l WHERE l.trader_nm LIKE N'Продукт 1' AND l.start_dt BETWEEN '20180531' AND '20190531'
29510.

Вопрос - в чем тут собака то зарыта?
0
Миниатюры
Посчитать размер клиентской за промежуток в год на дату  
Лучшие ответы (1)
QA
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
12.07.2019, 00:22
Ответы с готовыми решениями:

Как определить размер клиентской области окна Access?
Хочу при запуске растягивать форму по вертикали. По горизонтали не менять, т.е. это не...

Посчитать промежуток времени
Есть таблица id timestamp value quality мне нужно подсчитать сколько времени длилось каждое...

Посчитать, сколько элементов массива входит в промежуток [1;5]
Посчитать, сколько элементов массива входит в промежуток

Посчитать количество свободных мест за определенный промежуток времени
Существует база данных автовокзала, привязанная к приложению на C#. Нас интересуют таблица...

Как вывести дату за определенный квартал и год
Нужно в запросе вывести список фамилий за дату 3 квартал и 1983год SELECT Лист1., Лист1.Фамилия,...

13
naHuka
46 / 33 / 14
Регистрация: 12.09.2013
Сообщений: 167
12.07.2019, 01:15  [ТС] 2
Заметил косяк, но лучше не стало..

3 запрос, 11 строка, надо
SQL
1
LEFT JOIN cdm.loan AS l ON l.start_dt BETWEEN DATEADD(yy,-1,EOMONTH) AND eomonth
0
Мирзали
28 / 25 / 12
Регистрация: 20.11.2010
Сообщений: 454
12.07.2019, 07:47 3
Цитата Сообщение от naHuka Посмотреть сообщение
Вопрос - в чем тут собака то зарыта?
Цитата Сообщение от naHuka Посмотреть сообщение
BETWEEN '20180531' AND '20190531'
Пробовали писать дату в формате 'yyyy-mm-dd'?
0
iap
949 / 661 / 144
Регистрация: 27.11.2009
Сообщений: 1,996
12.07.2019, 09:47 4
Цитата Сообщение от Мирзали Посмотреть сообщение
Пробовали писать дату в формате 'yyyy-mm-dd'?
Интересно. А зачем?
0
12.07.2019, 09:47
invm
2298 / 1516 / 498
Регистрация: 02.06.2013
Сообщений: 3,738
12.07.2019, 09:57 5
Лучший ответ Сообщение было отмечено naHuka как решение

Решение

1. В контексте COUNT(DISTINCT l.client_key), LEFT JOIN cdm.client_risk_hist не имеет смысла
2. WHERE l.trader_nm IN (...) превращает LEFT JOIN cdm.loan AS l в INNER JOIN
1
naHuka
46 / 33 / 14
Регистрация: 12.09.2013
Сообщений: 167
13.07.2019, 11:00  [ТС] 6
invm, убрав cdm.client_risk_hist стал получать правильный ответ
Цитата Сообщение от invm Посмотреть сообщение
WHERE l.trader_nm IN (...) превращает LEFT JOIN cdm.loan AS l в INNER JOIN
Если в выбранном промежутке не было продаж указанных продуктов, то строчка всё равно вернётся.. или нет?
0
invm
2298 / 1516 / 498
Регистрация: 02.06.2013
Сообщений: 3,738
13.07.2019, 11:56 7
Цитата Сообщение от naHuka Посмотреть сообщение
Если в выбранном промежутке не было продаж указанных продуктов, то строчка всё равно вернётся.. или нет?
Строка из е? Нет, не вернется.
0
naHuka
46 / 33 / 14
Регистрация: 12.09.2013
Сообщений: 167
13.07.2019, 12:04  [ТС] 8
invm, А почему? Можете дать ссылку на источник, который объясняет такое поведение?
Никогда раньше не думал, что мои запросы с left join могут стать запросами с inner join, надо это исправлять
0
pincet
1411 / 990 / 136
Регистрация: 23.07.2010
Сообщений: 5,422
13.07.2019, 12:29 9
Цитата Сообщение от naHuka Посмотреть сообщение
Можете дать ссылку на источник, который объясняет такое поведение?
если я правильно понял вопрос - предложение where выполняется после from. и все дела
0
invm
2298 / 1516 / 498
Регистрация: 02.06.2013
Сообщений: 3,738
13.07.2019, 12:31 10
naHuka, потому что когда нет соответствующей условиям соединения строки из правой таблицы, значением ее столбцов будет null.
0
naHuka
46 / 33 / 14
Регистрация: 12.09.2013
Сообщений: 167
13.07.2019, 12:36  [ТС] 11
pincet, invm,

Именно об этом я и говорю.

Цитата Сообщение от invm Посмотреть сообщение
когда нет соответствующей условиям соединения строки из правой таблицы, значением ее столбцов будет null
Строчка из e, из таблицы, расположенной слева, не найдя соответствующей строки из правой таблицы присвоит столбцам правой таблицы значение NULL
0
invm
2298 / 1516 / 498
Регистрация: 02.06.2013
Сообщений: 3,738
13.07.2019, 12:41 12
Цитата Сообщение от naHuka Посмотреть сообщение
Строчка из e, из таблицы, расположенной слева, не найдя соответствующей строки из правой таблицы присвоит столбцам правой таблицы значение NULL
И тогда как сработает предикат l.trader_nm IN (...)?
0
naHuka
46 / 33 / 14
Регистрация: 12.09.2013
Сообщений: 167
13.07.2019, 12:45  [ТС] 13
invm, Выражение @value == NULL (проверить значение @value на равенство NULL) вернёт false. Следовательно это строка не вернётся. Следовательно выражение с left join вернёт те же строки, что и выражение с join..

Спасибо за разъяснения!
0
iap
949 / 661 / 144
Регистрация: 27.11.2009
Сообщений: 1,996
13.07.2019, 17:04 14
Цитата Сообщение от naHuka Посмотреть сообщение
invm, Выражение @value == NULL (проверить значение @value на равенство NULL) вернёт false. Следовательно это строка не вернётся. Следовательно выражение с left join вернёт те же строки, что и выражение с join..

Спасибо за разъяснения!
Нет. FALSE не вернёт. Здесь троичная логика. Выражение @value = NULL вернёт UNKNOWN.
А WHERE <logical expression> оставляет только строки, для которых получается TRUE.

Сравните, кстати, с условиями в констрейнте CHECK, который требует, в отличие от WHERE,
чтобы выражение возвращало NOT FALSE, пропуская таким образом NULLы.

Почитайте: https://docs.microsoft.com/ru-ru/sql...ql-server-2017
0
13.07.2019, 17:04
Answers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
13.07.2019, 17:04

Из DateTimePicker в DBGrid отобразить время, дату и год
Как написать код что бы из DateTimePicker в DBGrid отобразилась время, дата и год?

Вывести текущую дату (число, месяц, год)
Доброго времени суток! Подскажите, как устранить данные ошибки? (1) illegal instruction: LOCALS...

Вывести текущую дату (число, месяц, год)
Написать на языке ассемблера com-программу, которая позволит вывести текущую дату (число, месяц,...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
14
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Рейтинг@Mail.ru