Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
alexxUK Member Откуда: Украина Сообщений: 81 |
Доброго времени суток. Возник следующий вопрос - пока навскидку приходит понятное мне решение через CLR функции, но может кто подскажет более эффективное решение, если есть таковое. Проблема: Есть ответы от клиента по опросникам - и в некоторых ответах горе-программисты втулили привязку к вопросам - то есть можно встретить строку ответка формата: 071-R01-No,071-R02-No,071-R03-03,071-R04-01 В результате ответы одного человека могут выглядеть вот так: Yes 2 No 027-R01-Yes,027-R02-Yes,027-R03-01,027-R04-04 028-R01-Yes,028-R02-No,028-R03-02,028-R04-03 Из вот это радости нужно собрать финальную строку ответов - заменив все вхождения \d{3}-R\d{2}- на пустую строку. то есть в простом случае: 071-R01-No,071-R02-No,071-R03-03,071-R04-01 должно стать No,No,03,01 Нашел тут ссылки на вроде похожие темы - https://www.sql.ru/forum/571803/replace-s-opciey-match-whole-word?hl=sql regex replace https://www.sql.ru/forum/826622/regexreplace?hl=sql regex replace шас поизучаю матчасть, но пока решение в лоб - использование возможности студии - потом включение такой функции в базу и дергание ее (хотя стоит вопрос производительности.) Количество ответов от людей - пока порядка 50 000 уникальных пользователей - рост будет не быстрый - может до 10 000 в месяц. База данных = MSSQL 2008 R2 Да, код приложения поменять уже поздно - клиент не разрешает категорически. (хотя было бы разумнее накатить скрипт по удалению существующий вхождений и обновить сайт с фиксом). Заранее спасибо за ответы :) |
16 май 13, 17:01 [14306984] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
А вопрос то будет ? |
||
16 май 13, 17:02 [14306992] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
Извините, вопрос 1) Как собрать финальную строку ответов - заменив все вхождения \d{3}-R\d{2}- на пустую строку? 2) Остановиться ли на CLR функции для базы или это омжно написать только нативными средствами SQL Server 2008? то есть в простом случае: 071-R01-No,071-R02-No,071-R03-03,071-R04-01 должно стать No,No,03,01 |
16 май 13, 17:09 [14307043] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47048 |
alexxUK, чем же отличаются ответы одного человека от ответов другого? Напишите здесь CREATE TABLE и заполните примерными данными хотя бы для пары человек. И PRINT @@VERSION опубликуйте |
16 май 13, 17:09 [14307044] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
сейчас :), в процессе |
16 май 13, 17:10 [14307050] Ответить | Цитировать Сообщить модератору |
Гость333 Member Откуда: Сообщений: 3683 |
Вот решение на TSQL для простого случая: declare @s varchar(100) = '071-R01-No,071-R02-No,071-R03-03,071-R04-01'; declare @i int = 1; declare @length int = 8; while @i > 0 begin set @i = patindex('%[0-9][0-9][0-9]-R[0-9][0-9]-%', @s); if @i > 0 set @s = stuff(@s, @i, @length, ''); end; print @s; |
||
16 май 13, 17:11 [14307059] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
нужено распарсить строки на записи 027-R01-Yes,027-R02-Yes,027-R03-01,027-R04-04 на 027-R01-Yes 027-R02-Yes 027-R03-01 027-R04-04 А потом удалить все левее последнего - |
||
16 май 13, 17:12 [14307064] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
вот такие данные База: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SurveyResponseByPage]( [SysParticipantID] [bigint] NOT NULL, [SurveyID] [varchar](25) NOT NULL, [PageNumber] [int] NOT NULL, [Response] [varchar](2000) NULL CONSTRAINT [PK_SurveyResponseByPage] PRIMARY KEY CLUSTERED ( [SysParticipantID] ASC, [SurveyID] ASC, [PageNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Пример ужаса данных: Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 1, '001-Test Man1 ,002-Manager,003-Test Services,004-Alabama,005-IN,006-USA,007-12,008-Yes,009-8') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 2, '010-Male,011-1963,012-White') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 3, '013-test service,014-12,015-Yes,016-Do not know,017-Test Care,018-Test Facility,019-23,020-Yes,021-8,022-Yes,023-8,024-Yes,025-8,026-Yes') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 4, '027-R01-Yes,027-R02-Yes,027-R03-04,027-R04-05,028-R01-Yes,028-R02-Yes,028-R03-04,028-R04-04,029-R01-Yes,029-R02-Yes,029-R03-04,029-R04-05,030-R01-Yes,030-R02-Yes,030-R03-04,030-R04-05,031-R01-Yes,031-R02-Yes,031-R03-04,031-R04-04,032-R01-Yes,032-R02-Yes,032-R03-04,032-R04-05,033-R01-No,033-R02-No,033-R03-03,033-R04-03,034-R01-Yes,034-R02-Yes,034-R03-05,034-R04-05,035-R01-Yes,035-R02-Yes,035-R03-05,035-R04-05,036-R01-Yes,036-R02-Yes,036-R03-04,036-R04-05,037-R01-Yes,037-R02-Yes,037-R03-05,037-R04-05') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 5, '038-R01-Yes,038-R02-Yes,038-R03-05,038-R04-05,039-R01-Yes,039-R02-Yes,039-R03-05,039-R04-05,040-R01-Yes,040-R02-Yes,040-R03-05,040-R04-05,041-R01-Yes,041-R02-Yes,041-R03-05,041-R04-05,042-R01-Yes,042-R02-Yes,042-R03-04,042-R04-05,043-R01-Yes,043-R02-Yes,043-R03-05,043-R04-05,044-R01-Yes,044-R02-Yes,044-R03-05,044-R04-05,045-R01-Yes,045-R02-Yes,045-R03-04,045-R04-04,046-R01-No,046-R02-Yes,046-R03-04,046-R04-04,047-R01-Yes,047-R02-Yes,047-R03-05,047-R04-05,048-R01-No,048-R02-Yes,048-R03-04,048-R04-03,049-R01-Yes,049-R02-Yes,049-R03-05,049-R04-05,050-R01-Yes,050-R02-Yes,050-R03-05,050-R04-05') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 6, '051-R01-No,051-R02-No,051-R03-04,051-R04-04,052-R01-Yes,052-R02-Yes,052-R03-05,052-R04-05,053-R01-Yes,053-R02-Yes,053-R03-05,053-R04-05,054-R01-Yes,054-R02-Yes,054-R03-05,054-R04-05,055-R01-Yes,055-R02-Yes,055-R03-05,055-R04-05,056-R01-Yes,056-R02-Yes,056-R03-05,056-R04-05,057-R01-Yes,057-R02-Yes,057-R03-05,057-R04-05,058-R01-Yes,058-R02-Yes,058-R03-05,058-R04-05,059-R01-Yes,059-R02-Yes,059-R03-05,059-R04-05,060-R01-Yes,060-R02-Yes,060-R03-05,060-R04-05,061-R01-Yes,061-R02-Yes,061-R03-05,061-R04-05,062-R01-Yes,062-R02-Yes,062-R03-05,062-R04-05,063-R01-Yes,063-R02-Yes,063-R03-05,063-R04-05') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 7, '064-R01-Yes,064-R02-Yes,064-R03-04,064-R04-04,065-R01-Yes,065-R02-Yes,065-R03-05,065-R04-05,066-R01-Yes,066-R02-Yes,066-R03-05,066-R04-05,067-R01-Yes,067-R02-Yes,067-R03-05,067-R04-05,068-R01-Yes,068-R02-Yes,068-R03-05,068-R04-05,069-R01-Yes,069-R02-Yes,069-R03-05,069-R04-05,070-R01-Yes,070-R02-Yes,070-R03-05,070-R04-05,071-R01-Yes,071-R02-Yes,071-R03-05,071-R04-05,072-R01-Yes,072-R02-Yes,072-R03-05,072-R04-05,073-R01-Yes,073-R02-Yes,073-R03-05,073-R04-05,074-R01-No,074-R02-No,074-R03-03,074-R04-03,075-R01-Yes,075-R02-Yes,075-R03-04,075-R04-03,076-R01-Yes,076-R02-Yes,076-R03-05,076-R04-05') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 8, '077-01-Yes,077-02-No,077-03-No,077-04-Yes,077-05-No,077-06-Yes,077-07-No,077-08-No,077-09-Yes,077-10-No,077-11-No,077-12-No,078-01-Yes,078-02-No,078-03-No,078-04-No,078-05-No,078-06-No,078-07-No,078-08-No,078-09-No,078-10-No,078-11-No,078-12-No,079-01-Yes,079-02-No,079-03-No,079-04-No,079-05-No,079-06-No,079-07-No,079-08-No,079-09-No,079-10-No,079-11-No,079-12-No,080-01-Yes,080-02-No,080-03-No,080-04-No,080-05-No,080-06-No,080-07-No,080-08-No,080-09-No,080-10-No,080-11-No,080-12-No,081-01-Yes,081-02-No,081-03-No,081-04-No,081-05-No,081-06-No,081-07-No,081-08-No,081-09-No,081-10-No,081-11-No,081-12-No,082-01-Yes,082-02-No,082-03-No,082-04-No,082-05-No,082-06-No,082-07-No,082-08-No,082-09-No,082-10-No,082-11-No,082-12-No,083-01-Yes,083-02-No,083-03-No,083-04-No,083-05-No,083-06-No,083-07-No,083-08-No,083-09-No,083-10-No,083-11-No,083-12-No,084-01-Yes,084-02-No,084-03-No,084-04-No,084-05-No,084-06-No,084-07-No,084-08-No,084-09-No,084-10-No,084-11-No,084-12-No,085-01-Yes,085-02-No,085-03-No,085-04-No,085-05-No,085-06-No,085-07-No,085-08-No,085-09-No,085-10-No,085-11-No,085-12-No,086-01-Yes,086-02-No,086-03-No,086-04-No,086-05-No,086-06-No,086-07-No,086-08-No,086-09-No,086-10-No,086-11-No,086-12-No,087-01-Yes,087-02-No,087-03-No,087-04-No,087-05-No,087-06-No,087-07-No,087-08-No,087-09-No,087-10-No,087-11-No,087-12-No') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 9, '088-01-Yes,088-02-No,088-03-No,088-04-No,088-05-No,088-06-No,088-07-No,088-08-No,088-09-No,088-10-No,088-11-No,088-12-No,089-01-Yes,089-02-No,089-03-No,089-04-No,089-05-No,089-06-No,089-07-No,089-08-No,089-09-No,089-10-No,089-11-No,089-12-No,090-01-Yes,090-02-No,090-03-No,090-04-No,090-05-No,090-06-No,090-07-No,090-08-No,090-09-No,090-10-No,090-11-No,090-12-No,091-01-Yes,091-02-No,091-03-No,091-04-No,091-05-No,091-06-No,091-07-No,091-08-No,091-09-No,091-10-No,091-11-No,091-12-No,092-01-Yes,092-02-No,092-03-No,092-04-No,092-05-No,092-06-No,092-07-No,092-08-No,092-09-No,092-10-No,092-11-No,092-12-No,093-01-Yes,093-02-No,093-03-No,093-04-No,093-05-No,093-06-No,093-07-No,093-08-No,093-09-No,093-10-No,093-11-No,093-12-No,094-01-Yes,094-02-No,094-03-No,094-04-No,094-05-No,094-06-No,094-07-No,094-08-No,094-09-No,094-10-No,094-11-No,094-12-No,095-01-Yes,095-02-No,095-03-No,095-04-No,095-05-No,095-06-No,095-07-No,095-08-No,095-09-No,095-10-No,095-11-No,095-12-No,096-01-Yes,096-02-No,096-03-No,096-04-No,096-05-No,096-06-No,096-07-No,096-08-No,096-09-No,096-10-No,096-11-No,096-12-No,097-01-Yes,097-02-No,097-03-No,097-04-No,097-05-No,097-06-No,097-07-No,097-08-No,097-09-No,097-10-No,097-11-No,097-12-No,098-01-Yes,098-02-No,098-03-No,098-04-No,098-05-No,098-06-No,098-07-No,098-08-No,098-09-No,098-10-No,098-11-No,098-12-No,099-01-Yes,099-02-No,099-03-No,099-04-No,099-05-No,099-06-No,099-07-No,099-08-No,099-09-No,099-10-No,099-11-No,099-12-No') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 10, '100-01-Yes,100-02-No,100-03-No,100-04-No,100-05-No,100-06-No,100-07-No,100-08-No,100-09-No,100-10-No,100-11-No,100-12-No,101-01-Yes,101-02-No,101-03-No,101-04-No,101-05-No,101-06-No,101-07-No,101-08-No,101-09-No,101-10-No,101-11-No,101-12-No,102-01-Yes,102-02-No,102-03-No,102-04-No,102-05-No,102-06-No,102-07-No,102-08-No,102-09-No,102-10-No,102-11-No,102-12-No,103-01-Yes,103-02-No,103-03-No,103-04-No,103-05-No,103-06-No,103-07-No,103-08-No,103-09-No,103-10-No,103-11-No,103-12-No,104-01-Yes,104-02-No,104-03-No,104-04-No,104-05-No,104-06-No,104-07-No,104-08-No,104-09-No,104-10-No,104-11-No,104-12-No,105-01-Yes,105-02-No,105-03-No,105-04-No,105-05-No,105-06-No,105-07-No,105-08-No,105-09-No,105-10-No,105-11-No,105-12-No,106-01-Yes,106-02-No,106-03-No,106-04-No,106-05-No,106-06-No,106-07-No,106-08-No,106-09-No,106-10-No,106-11-No,106-12-No,107-01-Yes,107-02-No,107-03-No,107-04-No,107-05-No,107-06-No,107-07-No,107-08-No,107-09-No,107-10-No,107-11-No,107-12-No,108-01-Yes,108-02-No,108-03-No,108-04-No,108-05-No,108-06-No,108-07-No,108-08-No,108-09-No,108-10-No,108-11-No,108-12-No,109-01-Yes,109-02-No,109-03-No,109-04-No,109-05-No,109-06-No,109-07-No,109-08-No,109-09-No,109-10-No,109-11-No,109-12-No,110-01-Yes,110-02-No,110-03-No,110-04-No,110-05-No,110-06-No,110-07-No,110-08-No,110-09-No,110-10-No,110-11-No,110-12-No,111-01-Yes,111-02-No,111-03-No,111-04-No,111-05-No,111-06-No,111-07-No,111-08-No,111-09-No,111-10-No,111-11-No,111-12-No') Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response) Values(1, 'Test1', 11, '112-01-Yes,112-02-No,112-03-No,112-04-No,112-05-No,112-06-No,112-07-No,112-08-No,112-09-No,112-10-No,112-11-No,112-12-No,113-01-Yes,113-02-No,113-03-No,113-04-No,113-05-No,113-06-No,113-07-No,113-08-No,113-09-No,113-10-No,113-11-No,113-12-No,114-01-Yes,114-02-No,114-03-No,114-04-No,114-05-No,114-06-No,114-07-No,114-08-No,114-09-No,114-10-No,114-11-No,114-12-No,115-01-Yes,115-02-No,115-03-No,115-04-No,115-05-No,115-06-No,115-07-No,115-08-No,115-09-No,115-10-No,115-11-No,115-12-No,116-01-Yes,116-02-No,116-03-No,116-04-No,116-05-No,116-06-No,116-07-No,116-08-No,116-09-No,116-10-No,116-11-No,116-12-No,117-01-Yes,117-02-No,117-03-No,117-04-No,117-05-No,117-06-No,117-07-No,117-08-No,117-09-No,117-10-No,117-11-No,117-12-No,118-01-Yes,118-02-No,118-03-No,118-04-No,118-05-No,118-06-No,118-07-No,118-08-No,118-09-No,118-10-No,118-11-No,118-12-No,119-01-Yes,119-02-No,119-03-No,119-04-No,119-05-No,119-06-No,119-07-No,119-08-No,119-09-No,119-10-No,119-11-No,119-12-No,120-01-Yes,120-02-No,120-03-No,120-04-No,120-05-No,120-06-No,120-07-No,120-08-No,120-09-No,120-10-No,120-11-No,120-12-No,121-01-Yes,121-02-No,121-03-No,121-04-No,121-05-No,121-06-No,121-07-No,121-08-No,121-09-No,121-10-No,121-11-No,121-12-No,122-01-Yes,122-02-No,122-03-No,122-04-No,122-05-No,122-06-No,122-07-No,122-08-No,122-09-No,122-10-No,122-11-No,122-12-No,123-01-Yes,123-02-No,123-03-No,123-04-No,123-05-No,123-06-No,123-07-No,123-08-No,123-09-No,123-10-No,123-11-No,123-12-No') |
16 май 13, 17:27 [14307167] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
версия - до клиента достучаться не могу, но вроде вот такое будет Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM) |
16 май 13, 17:28 [14307178] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
идея интересная - это для случая не аггрегированных данных можно попробовать (промежуточная таблица), спасибо
была мысль положиться на дефис, но изначально не все описал, как вижу сейчас - возможно ответы клиента с дефисами в самом ответе. И да - некоторые содержат просто \d{3}- паттерн |
||||||||
16 май 13, 17:34 [14307216] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47048 |
alexxUK, всё-таки, надо формализовать. А то как же? |
16 май 13, 17:37 [14307238] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
не аггрегированные данные - тоже есть в базе Таблица SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SurveyAnswers]( [SysParticipantID] [bigint] NOT NULL, [SurveyID] [varchar](25) NOT NULL, [QuestionNumber] [int] NOT NULL, [PageNumber] [int] NULL, [QuestionResponse] [varchar](1000) NULL CONSTRAINT [PK_SurveyAnswers] PRIMARY KEY CLUSTERED ( [SysParticipantID] ASC, [SurveyID] ASC, [QuestionNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO Данные - выборки кусок: Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values( 1, 'Test1', 16 , 3, 'Do not know') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 17 , 3, 'Tets Care') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 18 , 3, 'Test Facility') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 19 , 3, '23') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 20 , 3, 'Yes') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 21 , 3, '8') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 22 , 3, 'Yes') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 23 , 3, '8') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 24 , 3, 'Yes') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 25 , 3, '8') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 26 , 3, 'Yes') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 27 , 4, '027-R01-Yes,027-R02-Yes,027-R03-04,027-R04-05') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 28 , 4, '028-R01-Yes,028-R02-Yes,028-R03-04,028-R04-04') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 29 , 4, '029-R01-Yes,029-R02-Yes,029-R03-04,029-R04-05') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 30 , 4, '030-R01-Yes,030-R02-Yes,030-R03-04,030-R04-05') Insert into surveyAnswers (SysParticipantID, SurveyID, QuestionNumber, PageNumber, QuestionResponse) Values(1, 'Test1', 31 , 4, '031-R01-Yes,031-R02-Yes,031-R03-04,031-R04-04') |
16 май 13, 17:47 [14307305] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
пока у меня есть стойкое желание сделать на С# = там мне просто понятнее, но если это затронет производительность = то тогда хочу сделать в базе ). |
16 май 13, 17:48 [14307319] Ответить | Цитировать Сообщить модератору |
Гость333 Member Откуда: Сообщений: 3683 |
Очень правильное желание.
Какую именно производительность вы имеете в виду? |
||||
16 май 13, 17:52 [14307343] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
Гость333, сейчас - сырой запрос (с неверными данными) по всем товарищам выдается порядка за 30 секунд - код кто-то тут наваял. Под производительностью - имеется ввиду - чтобы было время соизмеримое с текущей выборкой, учитывая что вообще regex будет немного тяжеловат (как мне кажется). Надо сделать и запустить несколько параллельных запросов - посмотреть, что скажет сервер на это (по ресурсам) :) |
16 май 13, 17:56 [14307379] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
итого - что навскидку получилось. 1) Сделал вот такую хрень private static readonly Regex longRegex = new Regex(@"\d{3}-R\d{2}-"); private static readonly Regex shortRegex = new Regex(@"\d{3}-"); /// <summary> /// function to perform text transformations for SQL Data /// </summary> /// <param name="reportData">actual string to modify</param> /// <param name="isToVerifyMarch">flag to identify if we need to perform validation for template occurence between transformation</param> /// <param name="isShortPattern">option to identify pattern that we need to use for transformations</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static SqlString RegexReplaceParticipantResponse(SqlString reportData, SqlBoolean isToVerifyMatch, SqlBoolean isShortPattern) { // return compiled string with performed replacements if (isShortPattern) { if (!isToVerifyMatch || shortRegex.IsMatch(reportData.Value)) { return new SqlString(shortRegex.Replace(reportData.Value, string.Empty)); } } else { if (!isToVerifyMatch || longRegex.IsMatch(reportData.Value)) { return new SqlString(longRegex.Replace(reportData.Value, string.Empty)); } } // no match has been found so far return reportData; } 2: разрешил серверу работать с такими функциями: -- 1 : reconfigure the DB to allow CLR sp_configure 'clr enabled', 1 RECONFIGURE WITH OVERRIDE 3: зарегистрировал ее на сервере (для базы): -- 2 : register assembly within MSSQL use [_YourDB_] CREATE ASSEMBLY RegX FROM '_YourPath\_YourDll_.dll' 4: cделал функцию в базе CREATE Function RegExReplace ( @reportData NVARCHAR(2000), @isToVerifyMatch BIT, @isShortPattern BIT ) RETURNS NVARCHAR(2000) EXTERNAL NAME RegX.UserDefinedFunctions.RegexReplaceParticipantResponse 5: Тестируем -- Testing Declare @sampleResultLong NVARCHAR(2000) Declare @sampleResultLongMod NVARCHAR(2000) Set @sampleResultLong = '027-R01-Yes,027-R02-Yes,027-R03-04,027-R04-05,028-R01-Yes,028-R02-Yes,028-R03-04,028-R04-04,029-R01-Yes,029-R02-Yes,029-R03-04,029-R04-05,030-R01-Yes,030-R02-Yes,030-R03-04,030-R04-05,031-R01-Yes,031-R02-Yes,031-R03-04,031-R04-04,032-R01-Yes,032-R02-Yes,032-R03-04,032-R04-05,033-R01-No,033-R02-No,033-R03-03,033-R04-03,034-R01-Yes,034-R02-Yes,034-R03-05,034-R04-05,035-R01-Yes,035-R02-Yes,035-R03-05,035-R04-05,036-R01-Yes,036-R02-Yes,036-R03-04,036-R04-05,037-R01-Yes,037-R02-Yes,037-R03-05,037-R04-05' Select @sampleResultLongMod = dbo.RegExReplace(@sampleResultLong, 'TRUE', 'FALSE') Print @sampleResultLongMod Declare @sampleResultShort NVARCHAR(2000) Declare @sampleResultShortMod NVARCHAR(2000) Set @sampleResultShort = '013-collections,014-less than 1 year,015-Yes,016-Do not know,017-Test - Collections,018-Test awesome Facility,019-3,020-Yes,021-3,022-No,023-,024-No,025-,026-Yes' Select @sampleResultShortMod = dbo.RegExReplace(@sampleResultShort, 'TRUE', 'TRUE') Print @sampleResultShortMod осталось только придумать - как эффективнее это все вызывать вывод одного результата для человека: 1) Таблица ResponseByPage : возможно есть смысл соединять строки 1-3 - так как они будут работать по облегченному паттерну - и менять их, а потом делать то же со строками 4-конец (в них вводится второй шаблон замены)- и эти 2 результата склеивать потом 2) Таблица Response = делать выборку по всем айтемам до определенного номера вопроса - там где нет вставок, а потом соединять вопросы со вставками = 027-R01-, обновлять вторую часть и потом это все дело соединять с первой частью? Еще прикол, что это надо собрать для всех людей (критерием является surveyID) - а я в SQL мягко говоря, немного туповат ). Может кто пнет в нужном направлении - как это пооптимальнее собрать? Заранее спасибо за идеи и комментарии |
16 май 13, 19:55 [14307970] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
ожидаемый результат для одного человека, как пример - -- First part - easy one (use formalized table) DECLARE @FirstResults NVARCHAR(1000) SELECT @FirstResults = COALESCE(@FirstResults + ',', '') + ISNULL(QuestionResponse, '') FROM [MIGSurvey].[dbo].[ParticipantSurveyAnswers] Where SurveyID = 'Test1' AND SysParticipantID = 1 And PageNumber < 4 Order by QuestionNumber Print @FirstResults -- second part - use generic table to reduce amount of strings to work with DECLARE @SecondResults NVARCHAR(4000) SELECT @SecondResults = COALESCE(@SecondResults + ',', '') + ISNULL(QuestionResponse, '') FROM [MIGSurvey].[dbo].[ParticipantSurveyAnswers] Where SurveyID = 'Test1' AND SysParticipantID = 1 And PageNumber >= 4 Order by QuestionNumber SET @SecondResults = dbo.RegExReplace(@SecondResults, 'TRUE', 'FALSE') Print @SecondResults -- third part: final result for one person Declare @FinalResults NVARCHAR(max) SET @FinalResults = @firstResults + ',' + @SecondResults + ',' Print @FinalResults |
16 май 13, 20:22 [14308084] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Скажите, вот вы серьезно думаете хранить реляционные данные в одном символьном поле ? И при каждом запросе преобразовывать это поле к нужному виду ? |
||
17 май 13, 09:08 [14309343] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
Glory, :), повторюсь, решение уже досталось от предыдущих товарищей, оно как-то работает и клиент категорически отказывается что-то менять в существующем коде, так что "маемо шо маемо" - теперь надо минимизировать извините за выражение "*опу" и сделать как-то замену для отчетов. Замену строк реализовал через регекс - написано выше, постарался собрать отчет по этому горю - в итоге время выполнения порядка 60 сек - на 1100 уникальных строк отчета - что, конечно, более полная ...., чем думалось. пример запроса по всем товарищам, что придумалось вечером с формализированной таблицей (в этом деле не сильно силен - надо учиться ) :). - далеается 2 замены регекспом. Select distinct dbo.RegExReplaceCommon(substring( (Select ','+ST1.QuestionResponse AS [text()] From [MIGSurvey].[dbo].[SurveyAnswers] ST1 Where ST1.SysParticipantID = ST2.SysParticipantID AND ST1.QuestionResponse != 'NULL' -- ? need to replace with empty string ORDER BY ST1.QuestionNumber For XML PATH ('') ),2, 3000), 'FALSE') [Response] From [MIGSurvey].[dbo].[SurveyAnswers] ST2 Where ST2.SurveyID='Test1' AND ST2.QuestionResponse != 'NULL' -- -- ? need to replace with empty string Хотя вот здесь вполне логично делать только одну замену, так как первые данные идут без префиксов. Вот думаю, если сослаться на вторую таблицу (SurveyResponseByPage) - где записаны уже меньшее количество строк и джоинов будет меньше - то может будет немного повеселее. Если не получится чего-то хорошего, тогда возможно будем еще раз аргументированно обьяснять, и вводить другие обьекты в базу, чтобы это починить. :) |
17 май 13, 10:39 [14309838] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Какое решение то ? Парсить каждый раз поле ?
Код вы создаете сейчас. А я говорю про структуру. |
||||
17 май 13, 10:43 [14309864] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
Glory, И да, по второй части ... этот запрос будет вызываться возможно раз в день - поскольку там несколько параллельных и независимых типов - то несколько раз в сутки для всех. Хочется просто уже с минимальными потерями допилить это до чего то более или менее адекватного. Подумалось сделать кстати еще одну таблицу (мысли вслух, так сказать), куда бы процедура складывала результаты выполнения преобразования - и потом при вызове этого функционала - только допихивало новые записи в финальную таблицу с преобразованием, а потом делать уже просто выборку из финальной таблицы (забыл добавить, данные по клиенту каждый раз выгребаются все - без привязки к дням или еще чему то... почему так - никто не обьясняет, типа работает же). То есть в идеале - если новых записей нет, то мы просто всегда делаем уже select по готовым данным. Елинственное что - иногда данные могут быть обновлены клиентом позже - то есть надо будет еще удалять старые данные по ключу, клиента - если есть обновленная версия (дата модификации) - и возможно это стоит сделать отдельными таблицами - то есть каждый клиент будет иметь только свою таблицу вот таких суммарных ответов, и не будет наразберихи в момент вставки, обновлений, перестроений и прочего. Изменения кода не будет - будет только новая процедура и таблицы ). Насколько несерьезно это все звучит? |
17 май 13, 10:48 [14309923] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
код - что я создал - это отдельная функция, которая встраивается в фиквел и протсо делает регексп. Клиент не хочет менять существующее решение по сбору и сохранению таких данных, возможно тут недоразумение было. Но ему нужен отчет по этому - то есть добавлять что-то я могу, менять существующее - категорически нет. |
||||||
17 май 13, 10:51 [14309943] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
решение сбора и сохранения данных по опросам. |
||
17 май 13, 10:52 [14309955] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
alexxUK, Если кому интересно - вот такой запрос выполнился за 7 сек, вернув 1200 записей -- clear cache for testi purposes CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO select distinct dbo.RegExReplaceCommon( substring( (Select ',' + ST1.Response AS [text()] From [MIGSurvey].[dbo].SurveyResponseByPage ST1 Where ST1.SysParticipantID = ST2.SysParticipantID AND ST1.Response is not NULL ORDER BY ST1.PageNumber For XML PATH ('') ),2, 3000 ), 'FALSE') [Response] From [MIGSurvey].[dbo].[SurveyResponseByPage] ST2 Where ST2.SurveyID='Test1' AND ST2.Response is not NULL осталось только понять, почему выборка разная, получилась ), и как это можно еще соптимизировать |
17 май 13, 11:33 [14310300] Ответить | Цитировать Сообщить модератору |
alexxUK Member Откуда: Украина Сообщений: 81 |
ну и само тело метода - которое проверяет на вхождение 2 регекспа подряд = может и коряво немного, но для старта полета мысли что-то есть/// <summary> /// function to perform text transformations for SQL Data based on both validations /// </summary> /// <param name="reportData">actual string to modify</param> /// <param name="isToVerifyMarch">flag to identify if we need to perform validation for template occurence between transformation</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static SqlString RegexReplaceResponseCommon(SqlString reportData, SqlBoolean isToVerifyMatch) { // return compiled string with performed replacements if (!isToVerifyMatch || longRegex.IsMatch(reportData.Value)) { var tempString = longRegex.Replace(reportData.Value, string.Empty); if (!isToVerifyMatch || shortRegex.IsMatch(tempString)) { return new SqlString(shortRegex.Replace(tempString, string.Empty)); } } else { if (!isToVerifyMatch || shortRegex.IsMatch(reportData.Value)) { return new SqlString(shortRegex.Replace(reportData.Value, string.Empty)); } } // no matches has been found so far return reportData; } всем отписавшимся большое спасибо за ответы и советы ) |
17 май 13, 11:37 [14310331] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |