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

Откуда:
Сообщений: 1197
Привет.

Есть отчет который показывает продажи по магазинам(кол-во и сумму) и статистику по посетителям/покупателям за выбранный период. Магазины могут иметь статусы: открыт, закрыт, временно не работает.

Задача.
Если в настоящий момент у магазина статус: закрыт, временно не работает. То показывать данные по данному магазину только если у него один из нижеперечисленных параметров не равен 0:
кол-во продаж, сумма продаж, кол-во посетителей, кол-во покупателей.

кол-во продаж и сумма продаж хранятся в одной базе, кол-во посетителей и кол-во покупателей хранятся в другой базе но в том же инстансе

Вопрос: как оптимальней организовать такую логику?
6 авг 13, 10:31    [14667849]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Гость333
Member

Откуда:
Сообщений: 3683
SELECT магазин, [кол-во продаж], [сумма продаж], [кол-во посетителей], [кол-во покупателей]
FROM магазины
WHERE статус = 'открыт' OR [кол-во продаж] <> 0 OR [сумма продаж] <> 0 OR [кол-во посетителей] <> 0 OR [кол-во покупателей] <> 0
6 авг 13, 10:37    [14667892]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Glory
Member

Откуда:
Сообщений: 104760
relief
Вопрос: как оптимальней организовать такую логику?

Как оптмальнее показывать только те записи родительской таюблицы, у которых есть записи в дочерней таблице ?
6 авг 13, 10:38    [14667902]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
relief
Member

Откуда:
Сообщений: 1197
Glory
relief
Вопрос: как оптимальней организовать такую логику?

Как оптмальнее показывать только те записи родительской таюблицы, у которых есть записи в дочерней таблице ?


в двух дочерних таблицах (продажи и статистика).
Ну и плюс нужно показывать еще брать инфу из этих дочерних таблиц: суммирование как раз по этим полям.

Гость 333, в принципе так и сделал, только функцию в конец поставил т.к. там на самом деле 3 статуса как "открыт". Подумал что вызов функции будет дороже нежели операция сравнения
6 авг 13, 10:54    [14667976]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Glory
Member

Откуда:
Сообщений: 104760
relief
в двух дочерних таблицах (продажи и статистика).

INNER JOIN автоматически отфильтрует те родительские записи, у которых нет дочерних
6 авг 13, 10:57    [14667996]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Гость333
Member

Откуда:
Сообщений: 3683
relief
только функцию в конец поставил т.к. там на самом деле 3 статуса как "открыт". Подумал что вызов функции будет дороже нежели операция сравнения

Какую функцию? В конец чего поставили?
6 авг 13, 11:12    [14668085]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
relief
Member

Откуда:
Сообщений: 1197
Glory
relief
в двух дочерних таблицах (продажи и статистика).

INNER JOIN автоматически отфильтрует те родительские записи, у которых нет дочерних


не пойдет. в некоторых случаях не нужна инфа из таблицы со статистикой по посетителям.
т.е. нужно просто магазин - сумма - кол-во продаж
Но надо учитывать есть ли инфа по статистике кол-ва посетителей

вы предлагаете тут тоже через inner join?
6 авг 13, 11:14    [14668098]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Glory
Member

Откуда:
Сообщений: 104760
relief
не пойдет. в некоторых случаях не нужна инфа из таблицы со статистикой по посетителям.

relief
То показывать данные по данному магазину только если у него один из нижеперечисленных параметров не равен 0:
кол-во продаж, сумма продаж, кол-во посетителей, кол-во покупателей.

Сколько условий в задаче то ?
6 авг 13, 11:16    [14668115]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
relief
Member

Откуда:
Сообщений: 1197
Glory
relief
не пойдет. в некоторых случаях не нужна инфа из таблицы со статистикой по посетителям.

relief
То показывать данные по данному магазину только если у него один из нижеперечисленных параметров не равен 0:
кол-во продаж, сумма продаж, кол-во посетителей, кол-во покупателей.

Сколько условий в задаче то ?


1. если у магазина статус = "Активен", "Активен2","Активен3" то его и его инфу показывать всегда
2. если у магазина статус = "Закрыт", "Временно не работает" то его и его инфу показывать если один из параметров != 0
а. Кол-во продаж
б. сумма продаж
в. кол-во посететелй
г. кол-во покупателей

Гость 333,

SELECT магазин, [кол-во продаж], [сумма продаж], [кол-во посетителей], [кол-во покупателей]
FROM магазины
WHERE [кол-во продаж] <> 0 OR [сумма продаж] <> 0 OR [кол-во посетителей] <> 0 OR [кол-во покупателей] <> 0 OR 
[b][dbo][sf_IsStausOk](status) = 1[/b]
6 авг 13, 11:22    [14668161]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Гость333
Member

Откуда:
Сообщений: 3683
relief,

То есть вы полагаете, что сервер выполняет проверки в соответствии с тем порядком, который вы задали в списке OR?
sf_IsStausOk — можно увидеть исходный текст этой функции?
6 авг 13, 11:40    [14668295]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
relief
Member

Откуда:
Сообщений: 1197
Гость333
relief,

То есть вы полагаете, что сервер выполняет проверки в соответствии с тем порядком, который вы задали в списке OR?
sf_IsStausOk — можно увидеть исходный текст этой функции?


1. да. думал как в компиляторе C#
2.
FUNCTION [dbo].[sf_IsStatusOk]
(
	@Status INT
)
RETURNS BIT
AS
BEGIN
	
	DECLARE @IsStatusOk BIT 

	SELECT @IsStatusOk =
	  CASE 
	    WHEN @Status IN (5, 8, 10) THEN 1 
		ELSE 0
	  END

	-- Return the result of the function
	RETURN @IsStatusOk

END
6 авг 13, 12:02    [14668462]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А вам функция нужна только для этого запроса?

Лучше APPLY оформить, меньше буковок будет.
6 авг 13, 12:21    [14668618]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
relief
OR [dbo][sf_IsStausOk](status) = 1
Очень медленно будет :-(

Лучше вынести текст функции в запрос.
6 авг 13, 12:27    [14668687]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Гость333
Member

Откуда:
Сообщений: 3683
relief
Гость333
relief,

То есть вы полагаете, что сервер выполняет проверки в соответствии с тем порядком, который вы задали в списке OR?

1. да. думал как в компиляторе C#

Это не так. Оптимизатор сам выбирает, в какой последовательности выгоднее будет сделать проверки.

Как кто-то говорил на этом форуме — добро пожаловать в мир декларативного программирования! :-)
6 авг 13, 12:30    [14668712]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
relief
1. да. думал как в компиляторе C#
Видно что вы даже не представляете что такое декларативное программирование. Более того - вы то и C# не знаете.
relief
WHEN @Status IN (5, 8, 10) THEN 1 ELSE 0
А тут вы сначала выкручиваете мозги системе что заменяете вторичное понятие первичным, а затем вправляете это обратно.
Статус/Состояние - это вторичное понятие, оно вычисляется (его имя, идентификатор в справочнике) на основании свойств объекта - а не наоборот.
Тогда вам не нужно будет писать много кода, притом не эффективного до жути. А тупо: Opened = 1.

relief
Если в настоящий момент у магазина статус: закрыт, временно не работает. То показывать данные по данному магазину только если у него один из нижеперечисленных параметров не равен 0:
Включите логику - вам надо отображать все магазины без всякого фильтра по статистике. + открытые магазины без статистики.
Ни одного OR не надо.

Создаёте агрегирующую вью по магазинам, вешаете на неё индекс и просто из неё делаете запрос.

PS: Лето - время нубов не только в вопросах, но и медвежьих советов.
6 авг 13, 12:32    [14668730]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
relief
Member

Откуда:
Сообщений: 1197
alexeyvg
relief
OR [dbo][sf_IsStausOk](status) = 1
Очень медленно будет :-(

Лучше вынести текст функции в запрос.



так сначала и было. но много где это применяется.
ок, верну как было
6 авг 13, 12:57    [14668957]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
relief
Member

Откуда:
Сообщений: 1197
Mnior
relief
1. да. думал как в компиляторе C#
Видно что вы даже не представляете что такое декларативное программирование. Более того - вы то и C# не знаете.
relief
WHEN @Status IN (5, 8, 10) THEN 1 ELSE 0
А тут вы сначала выкручиваете мозги системе что заменяете вторичное понятие первичным, а затем вправляете это обратно.
Статус/Состояние - это вторичное понятие, оно вычисляется (его имя, идентификатор в справочнике) на основании свойств объекта - а не наоборот.
Тогда вам не нужно будет писать много кода, притом не эффективного до жути. А тупо: Opened = 1.

relief
Если в настоящий момент у магазина статус: закрыт, временно не работает. То показывать данные по данному магазину только если у него один из нижеперечисленных параметров не равен 0:
Включите логику - вам надо отображать все магазины без всякого фильтра по статистике. + открытые магазины без статистики.
Ни одного OR не надо.

Создаёте агрегирующую вью по магазинам, вешаете на неё индекс и просто из неё делаете запрос.

PS: Лето - время нубов не только в вопросах, но и медвежьих советов.


Opened = 1 - не пойдет, т.к. я сказал, что 3 статуса являются как Opened
Можно пример вью и запроса для следующего условия:

Показать инфу по магазинам за период март-апрель с условием что я описал?
6 авг 13, 13:06    [14669021]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
relief
alexeyvg
Очень медленно будет :-(

Лучше вынести текст функции в запрос.

так сначала и было. но много где это применяется.
ок, верну как было
К сожалению, скалярные функции очень медленно работают.

Можно заменить на инлайн-табличную функцию, можно сделать вьюху с этим расчитанным статусом, можно как Mnior написал
relief
Opened = 1 - не пойдет, т.к. я сказал, что 3 статуса являются как Opened
Так говорится, что Opened - некий агрегатный статус, он вычисляется по набору разных критериев. Это принципиально верный подход, не надо размазывать сложную логику в тыщах запросов по всему приложению. Держите такого рода логику в одном месте - в триггере, во вьюхе, в единственной процедуре и т.п.

Ваше решение держать такую логику в одной функции принципиально правильное, но в сиквеле скалярные функции медленные, поэтому такое решение не подходит.
6 авг 13, 15:44    [14670228]     Ответить | Цитировать Сообщить модератору
 Re: Ступенчатая логика при Select  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
relief
Opened = 1 - не пойдет, т.к. я сказал, что 3 статуса являются как Opened
Я о том же. Не надо было в статусы заворачивать свойства.
Вот чем отличаются статусы?
- Закрыт по финансовым причинам.
- Временно закрыт.
- Закрыт по техническим причинам.
Надо было одно поле: ClosedBy, а далее не важно по каким причинам и принципам.
У условие простое ClosedBy IS NOT NULL. И не важно что там в этом ClosedBy - ссылка на причину или период или тупо дата.
Вы просто не хотите понять, и то что вы тупо повторяете слово в слово, остальными читается, так "Иди на йюх, мне пофиг", что является оскорблением, а у каждого оскорбление по своему, у кого один набор слов, у кого другой.

relief
Показать инфу по магазинам за период март-апрель с условием что я описал?
Да чё тут показывать.
Если магазин закрыт, то и операций у него уже не появятся.
+ Полный тестовый код с разнообразными вариантами
USE tempdb
GO
CREATE TABLE dbo.TShop (
	 ID	Int	IDENTITY
	 CONSTRAINT PK_TShop PRIMARY KEY
	,Name	NVarChar(250)	NOT NULL
	,Closed	Date		    NULL
)
CREATE INDEX IX_Opened ON dbo.TShop (ID) INCLUDE (Name) WHERE (Closed IS NULL)
GO
CREATE TABLE dbo.TSelling (
	 ID		Int		IDENTITY
	 CONSTRAINT PK_TSelling PRIMARY KEY
	,Shop		Int		NOT NULL
	 CONSTRAINT FK_TSelling_TShop REFERENCES dbo.TShop (ID)
	,Product	Int		NOT NULL
--	 CONSTRAINT FK_TSelling_TShop REFERENCES dbo.TProduct (ID)
	,[Date]		DateTime	NOT NULL
	,Amount		Money		NOT NULL
)
CREATE INDEX IX_Shop_Date ON dbo.TSelling (Shop,[Date])
GO
CREATE VIEW dbo.VShopSellingStatMonth WITH SCHEMABINDING AS
SELECT	 S.Shop
	,Convert(Date,DateAdd(Month,DateDiff(Month,0,S.[Date]),0))
			AS [Month]
	,Count_Big(*)	AS [Count]
	,Sum(S.Amount)	AS Total
FROM	dbo.TSelling	S
GROUP BY S.Shop
	,Convert(Date,DateAdd(Month,DateDiff(Month,0,S.[Date]),0))
GO
CREATE UNIQUE CLUSTERED INDEX PK_VShopSellingStatMonths ON dbo.VShopSellingStatMonth(Shop,[Month])
GO
INSERT dbo.TShop VALUES
 ('Рога'	,NULL)
,('Копыта'	,'20130601')
,('Веник'	,NULL)
,('Ромашка'	,'20130501')
INSERT dbo.TSelling VALUES
 (1,1,'20130315',3.50)
,(1,2,'20130416',100.500)
,(1,2,'20130517',12.34)
,(2,1,'20130404',4.04)
GO
	SELECT	 M.ID
		,M.Name
		,S.[Month]
		,S.[Count]
		,S.Total
	FROM	dbo.VShopSellingStatMonth	S -- WITH (NoExpand) LEFT
	JOIN	dbo.TShop			M ON M.ID = S.Shop
	WHERE	S.[Month] >= '20130301'	-- @From
	AND	S.[Month] <  '20130501'	-- @To
	-- Можно и групировать, зависит от представления отчёта
UNION ALL
	SELECT	 M.ID
		,M.Name
		,'20130301'	AS [Month]	-- @From
		,0		AS [Count]
		,0		AS Total
	FROM	dbo.TShop	M
	WHERE	M.Closed IS NULL
	AND NOT Exists(	SELECT	*
			FROM	dbo.VShopSellingStatMonth	S
			WHERE	S.Shop     = M.ID
			AND	S.[Month] >= '20130301'	-- @From
			AND	S.[Month] <  '20130501'	-- @To
	)
GO
SELECT	 *
	,IsNull(S.[Count],0)	AS [Count]
	,IsNull(S.Total  ,0)	AS Total
FROM	dbo.TShop	M OUTER APPLY (
	SELECT	 Sum(S.[Count])
		,Sum(S.Total)
	FROM	dbo.VShopSellingStatMonth	S
	WHERE	S.Shop     = M.ID
	AND	S.[Month] >= '20130301'	-- @From
	AND	S.[Month] <  '20130501'	-- @To
		)	S([Count],Total)
WHERE	S.[Count] IS NOT NULL
OR	M.Closed  IS     NULL
-- Не используется индекс, но считаем что в основном все магазины работают
-- Но если большинство магазинов закрыто, то первый вариант лучше
GO
-- Так феншуйнее
SELECT	 *
	,IsNull(S.[Count],0)	AS [Count]
	,IsNull(S.Total  ,0)	AS Total
FROM	dbo.TShop	M LEFT JOIN (
	SELECT	 S.Shop
		,Sum(S.[Count])
		,Sum(S.Total)
	FROM	dbo.VShopSellingStatMonth	S
	WHERE	S.[Month] >= '20130301'	-- @From
	AND	S.[Month] <  '20130501'	-- @To
	GROUP BY S.Shop)S(Shop,[Count],Total)
			ON S.Shop = M.ID
WHERE	S.[Count] IS NOT NULL
OR	M.Closed  IS     NULL
GO
DROP VIEW  dbo.VShopSellingStatMonth;
DROP TABLE dbo.TSelling, dbo.TShop;
GO
-- Если отчёт редкий и скорость не нужна, то можно и без PK_VShopSellingStatMonths и других индексов
-- На свой выбор
6 авг 13, 15:50    [14670279]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить