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

Откуда:
Сообщений: 54
Добрый день!

Есть таблица TABLE вида (прошу не просить оптимизировать, так как именно такая форма необходима для отображения в SCADA-системе):

ID_Замеса | Дата_Время | Клиент | ID_материала_из_бункера_1 | Вес_материала_из_бункера_1 | ID_материала_из_бункера_2 | Вес_материала_из_бункера_2 ... ID_материала_из_бункера_8 | Вес_материала_из_бункера_8 | Сумма_материалов


Примеры:

1 | 2012.10.31 10:34:57 | Иванов | 1 | 567.5 | 2 | 244.5 | .... | 8 | 34.2 | 1000
2 | 2012.10.31 11:38:23 | Петров | 2 | 456.5 | 1 | 555.5 | .... | 17 | 777.2 | 2000
3 | 2012.10.31 13:55:23 | Сидоров | 6 | 444.4 | 15 | 222.2 | .... | 7 | 99.9 | 1700


Надо составить отчет (сумму) по 30 видам материалов (справочник материалов будет ограничен 30 записями) за заданный диапазон времени, ну и по клиенту.


Помогите написать оптимальный по выполнению запрос и по каким полям создавать индексы/индекс?
2 ноя 12, 10:00    [13412460]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
iobox
Member

Откуда:
Сообщений: 54
Ах, да. Есть еще справочник материалов вида:

ID_материала | Название
2 ноя 12, 10:02    [13412477]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
Добрый Э - Эх
Guest
iobox
Есть таблица TABLE вида (прошу не просить оптимизировать, так как именно такая форма необходима для отображения в SCADA-системе):

Хранение данных и их отображения - две независимые задачи. Кто мешает сделать таблицу с нормальной структурой, а для отображения её в SCADA-системе использовать представление по \этой таблицы с нужным видом результирующего набора данных?
2 ноя 12, 10:22    [13412602]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
iobox
Member

Откуда:
Сообщений: 54
Представление (VIEW) создается средствами SCADA системы. А там по сути UNION по определенным полям по условиям = или !=. Короче, не подходит этот вариант.

Еще есть идея ведения второй (дублирующей таблицы) по триггеру на INSERT или UPDATE. Но это крайний случай, так как две базы - расточительно. Тем более, что за год будет под 100 000 записей в вышеуказанной таблице. А во второй и подавно больше.
2 ноя 12, 10:29    [13412630]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
Добрый Э - Эх
Guest
iobox
Представление (VIEW) создается средствами SCADA системы.
пересоздать его средствами MS SQL Server-а
2 ноя 12, 10:35    [13412665]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
Добрый Э - Эх
Guest
по поводу запроса...
давай тестовый набор данных и что должно быть на выходе.
может тогда и запрос кто напишет...
2 ноя 12, 10:36    [13412674]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
iobox
Member

Откуда:
Сообщений: 54
Спасибо за совет!
Но учет реализован именно в таком виде потому что:

1. Это SCADA - система, которая позволяет создавать таблицы и представления через свой интерфейс и соответственно отображать через свой ActiveX компонент, который не потерпит "кривых таблиц и представлений" - то есть нет гарантии безотказной работы.

2. Есть возможность работы через ADO на VB. Но скрипты весьма медленно выполняются и возможно подвисание из-за сложных скриптов (читай - запросов) скриптовой подсистемы.

3. Он уже реализован и именно в таком виде, лишние изменения ни к чему, надо всего лишь подготовить данные для вывода в пару отчетов.
2 ноя 12, 10:43    [13412730]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
Добрый Э - Эх
Guest
iobox
Но учет реализован именно в таком виде .
Ладно, с этим понятно.

Давай тестовые данные в виде WITH t as..., и желаемый вид результата на этих тестовых данных. Будем пилить тебе запросы...
2 ноя 12, 10:59    [13412819]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
iobox
Member

Откуда:
Сообщений: 54
USE [CC_DP1-GATE_12_04_12_10_37_35R]
GO
/****** Object: Table [dbo].[UA#AC_BSU] Script Date: 11/02/2012 10:52:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UA#AC_BSU](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AC_ID] [int] NULL,
[AC_Bun1_Mat] [int] NULL,
[AC_Bun2_Mat] [int] NULL,
[AC_Bun3_Mat] [int] NULL,
[AC_Bun4_Mat] [int] NULL,
[AC_Bun5_Mat] [int] NULL,
[AC_Bun6_Mat] [int] NULL,
[AC_Bun7_Mat] [int] NULL,
[AC_Bun8_Mat] [int] NULL,
[AC_Bun1_Mat_Weight] [float] NULL,
[AC_Bun2_Mat_Weight] [float] NULL,
[AC_Bun3_Mat_Weight] [float] NULL,
[AC_Bun4_Mat_Weight] [float] NULL,
[AC_Bun5_Mat_Weight] [float] NULL,
[AC_Bun6_Mat_Weight] [float] NULL,
[AC_Bun7_Mat_Weight] [float] NULL,
[AC_Bun8_Mat_Weight] [float] NULL,
[AC_DateTime] [datetime] NULL,
[Fingerprint] [nvarchar](34) NULL,
CONSTRAINT [PK__UA#PRE#AC_BSU__3DD43118] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



INSERT INTO [UA#AC_BSU]
([AC_ID]
,[AC_Bun1_Mat]
,[AC_Bun2_Mat]
,[AC_Bun3_Mat]
,[AC_Bun4_Mat]
,[AC_Bun5_Mat]
,[AC_Bun6_Mat]
,[AC_Bun7_Mat]
,[AC_Bun8_Mat]
,[AC_Bun1_Mat_Weight]
,[AC_Bun2_Mat_Weight]
,[AC_Bun3_Mat_Weight]
,[AC_Bun4_Mat_Weight]
,[AC_Bun5_Mat_Weight]
,[AC_Bun6_Mat_Weight]
,[AC_Bun7_Mat_Weight]
,[AC_Bun8_Mat_Weight]
,[AC_DateTime])
VALUES (1,1,2,3,4,5,6,7,8,20,30,40,50,60,70,80,90,'2012-10-31 10:10:10')

INSERT INTO [UA#AC_BSU]
([AC_ID]
,[AC_Bun1_Mat]
,[AC_Bun2_Mat]
,[AC_Bun3_Mat]
,[AC_Bun4_Mat]
,[AC_Bun5_Mat]
,[AC_Bun6_Mat]
,[AC_Bun7_Mat]
,[AC_Bun8_Mat]
,[AC_Bun1_Mat_Weight]
,[AC_Bun2_Mat_Weight]
,[AC_Bun3_Mat_Weight]
,[AC_Bun4_Mat_Weight]
,[AC_Bun5_Mat_Weight]
,[AC_Bun6_Mat_Weight]
,[AC_Bun7_Mat_Weight]
,[AC_Bun8_Mat_Weight]
,[AC_DateTime])
VALUES (2,11,12,13,14,15,16,17,18,20,30,40,50,60,70,80,90,'2012-10-31 11:11:11')

INSERT INTO [UA#AC_BSU]
([AC_ID]
,[AC_Bun1_Mat]
,[AC_Bun2_Mat]
,[AC_Bun3_Mat]
,[AC_Bun4_Mat]
,[AC_Bun5_Mat]
,[AC_Bun6_Mat]
,[AC_Bun7_Mat]
,[AC_Bun8_Mat]
,[AC_Bun1_Mat_Weight]
,[AC_Bun2_Mat_Weight]
,[AC_Bun3_Mat_Weight]
,[AC_Bun4_Mat_Weight]
,[AC_Bun5_Mat_Weight]
,[AC_Bun6_Mat_Weight]
,[AC_Bun7_Mat_Weight]
,[AC_Bun8_Mat_Weight]
,[AC_DateTime])
VALUES (3,9,10,12,17,2,1,3,5,20,30,40,50,60,70,80,90,'2012-10-31 12:12:12')
2 ноя 12, 11:03    [13412844]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
icprog
Member

Откуда:
Сообщений: 166
Так, а что хотелось бы получить выходе ?
2 ноя 12, 11:05    [13412858]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
iobox
Member

Откуда:
Сообщений: 54
Надо получить данные в виде:

ID_Mat |Sum(Weight_Mat)
1|0
2|56.5
...
30|100.1
2 ноя 12, 11:06    [13412876]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
iobox
Member

Откуда:
Сообщений: 54
Вообще смысл такой: несколько бункеров с материалами (могут быть одинаковые и разные одновременно), материал в бункере со временем может поменяться - засыпят другой. Надо за определенное время подсчитать расход каждого вида материала из любого бункера. Запрос в "лоб" я напишу, но есть ли элегантные решения?
2 ноя 12, 11:11    [13412916]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
icprog
Member

Откуда:
Сообщений: 166
select ID_Mat, SUM(Weigth) FROM (
select AC_BUN1_Mat AS ID_Mat, AC_Bun1_Mat_Weight AS Weigth from UA#AC_BSU
UNION ALL
select AC_BUN2_Mat, AC_Bun2_Mat_Weight from UA#AC_BSU
UNION ALL
select AC_BUN3_Mat, AC_Bun3_Mat_Weight from UA#AC_BSU
UNION ALL
select AC_BUN4_Mat, AC_Bun4_Mat_Weight from UA#AC_BSU
UNION ALL
select AC_BUN5_Mat, AC_Bun5_Mat_Weight from UA#AC_BSU
UNION ALL
select AC_BUN6_Mat, AC_Bun6_Mat_Weight from UA#AC_BSU
UNION ALL
select AC_BUN7_Mat, AC_Bun7_Mat_Weight from UA#AC_BSU
UNION ALL
select AC_BUN8_Mat, AC_Bun8_Mat_Weight from UA#AC_BSU
) sub_table
group by ID_Mat
2 ноя 12, 11:18    [13412979]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
icprog
Member

Откуда:
Сообщений: 166
ну и не забыть условие конечно
2 ноя 12, 11:23    [13413022]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
iobox
Member

Откуда:
Сообщений: 54
icprog, спасибо! именно такая идея у меня и была в принципе. но есть сомнения, что на 100 000 записях это будет не очень быстро работать. по каким полям создать индексы? кстати, еще будут условия заказчик и дата_время, да и всего будет 15 емкостей - это я уже сам доделаю.
2 ноя 12, 11:26    [13413040]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
iobox
Member

Откуда:
Сообщений: 54
условия будут в каждом под'select?
2 ноя 12, 11:30    [13413082]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
Добрый Э - Эх
Guest
почитай про PIVOT / UNPIVOT. тогда и надобности 8 раз лазать в таблицу не будет
2 ноя 12, 11:33    [13413119]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
icprog
Member

Откуда:
Сообщений: 166
Через UNPIVOT
SELECT ID_Mat, SUM(Mat_Weight)
FROM (
SELECT ID, AC_DateTime, AC_Bun1_Mat, AC_Bun2_Mat, AC_Bun3_Mat, AC_Bun4_Mat, AC_Bun5_Mat, AC_Bun6_Mat, AC_Bun7_Mat, AC_Bun8_Mat,
AC_Bun1_Mat_Weight, AC_Bun2_Mat_Weight, AC_Bun3_Mat_Weight, AC_Bun4_Mat_Weight, AC_Bun5_Mat_Weight,
AC_Bun6_Mat_Weight, AC_Bun7_Mat_Weight, AC_Bun8_Mat_Weight 
FROM UA#AC_BSU
) main
UNPIVOT
(ID_Mat FOR Material IN (AC_Bun1_Mat, AC_Bun2_Mat, AC_Bun3_Mat, AC_Bun4_Mat, AC_Bun5_Mat, AC_Bun6_Mat, AC_Bun7_Mat, AC_Bun8_Mat)) Mat
UNPIVOT
(Mat_Weight FOR Mt_Weight IN (AC_Bun1_Mat_Weight, AC_Bun2_Mat_Weight, AC_Bun3_Mat_Weight, AC_Bun4_Mat_Weight, AC_Bun5_Mat_Weight, AC_Bun6_Mat_Weight, AC_Bun7_Mat_Weight, AC_Bun8_Mat_Weight)) Mat
WHERE SUBSTRING(Material, 7,1)=SUBSTRING(Mt_Weight, 7,1)
GROUP BY ID_Mat
2 ноя 12, 12:08    [13413409]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос и создать индексы  [new]
iobox
Member

Откуда:
Сообщений: 54
Спасибо огромное! Вы сэкономили мне целый грядущий выходной! Подгоняю запрос под реальный проект.
2 ноя 12, 14:00    [13414279]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить