#sql #sql-server
#sql #sql-сервер
Вопрос:
Я должен преобразовать одну строку в несколько строк на основе числовых значений в строке в одном столбце
Пример ввода:
EmpId | work date | String
------ ------------ --------------------------------------------------------
1234 | 12/10/2020 | The following clocks 12:03,12:04 are outside of the allowed radius by 209759,209758 meters
Пример вывода:
Empid | Work Date | Clock | Radius
------ ------------ ------- --------
1234 | 12/10/2020 | 12:03 | 209759
1234 | 12/10/2020 | 12:04 | 209758
Может быть n значений, основанных на числах в строке, которые необходимо разделить на два столбца и строки.
Пожалуйста, помогите мне с этим — спасибо
Комментарии:
1. Пожалуйста, укажите правила для этого. Например,
12:03это не «числовое значение», как я бы определил «числовое». Их всегда два? Что, если существует несколько групп? И почему вы используете неподдерживаемую версию SQL Server?2. SQL действительно плох в подобных манипуляциях со строками. Сделайте это на C # или подобном. Лучшая идея, сначала поместите ее в базу данных в отдельные строки и столбцы
3. Привет, я новичок в Microsoft Sql server. версия сервера, которую я использую, равна 15, их будет много, не ограничиваясь двумя, в строке будет больше. мне нужно разделить его на основе, и он должен отображаться в двух отдельных столбцах
4. Если вы знаете, что текст всегда имеет форму
The following clocks [list of times] are outside of the allowed radius by [list of integers] meters, то это вполне выполнимо в T-SQL, используяCHARINDEXи любое количество хорошо известных подходов разделения строк, еслиSTRING_SPLITоно недоступно. Для любого шаблона, более общего, чем этот, он становится сомнительным. Я согласен с другими, что вам гораздо лучше определить процесс, который генерирует этот текст в первую очередь, и заставить его создавать строки вместо текста.
Ответ №1:
Пожалуйста, попробуйте следующее решение.
Это грязно, но работает:
- Первый CTE маркирует столбцы free_text с помощью XML и XQuery и отфильтровывает токены без запятой.
- 2-й CTE получает столбец часов из XML.
- 3-й CTE получает столбец Radius из XML.
- Окончательный ВЫБОР объединяет все это.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (emp_id INT, work_date DATE, free_text NVARCHAR(MAX))
INSERT INTO @tbl (emp_id, work_date, free_text) VALUES
(1234, '12/10/2020',N'The following clocks 12:03,12:04 are outside of the allowed radius by 209759,209758 meters');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = SPACE(1)
, @comma CHAR(1) = ',';
WITH rs AS
(
SELECT emp_id, work_date
, CAST('<root><r><![CDATA['
REPLACE(free_text COLLATE Czech_BIN2, @separator, ']]></r><r><![CDATA[') ']]></r></root>' AS XML)
.query('
for $x in /root/r
where contains($x, sql:variable("@comma"))
return $x
') AS result
FROM @tbl
), clock AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
, rs.*
, z.value AS clock
FROM rs
CROSS APPLY result.nodes('/r[1]') AS t(c)
CROSS APPLY STRING_SPLIT(c.value('.', 'VARCHAR(20)'), @comma) AS z
), Radius AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
, rs.*
, z.value AS Radius
FROM rs
CROSS APPLY result.nodes('/r[2]') AS t(c)
CROSS APPLY STRING_SPLIT(c.value('.', 'VARCHAR(20)'), @comma) AS z
)
SELECT c.emp_id, c.work_date, c.clock, r.Radius
FROM clock AS c
INNER JOIN Radius AS r ON r.seq = c.seq
AND r.emp_id = c.emp_id;
Вывод
-------- ------------ ------- --------
| emp_id | work_date | clock | Radius |
-------- ------------ ------- --------
| 1234 | 2020-12-10 | 12:03 | 209759 |
| 1234 | 2020-12-10 | 12:04 | 209758 |
-------- ------------ ------- --------
Ответ №2:
Еще один беспорядочный вариант — без XML, но с использованием заимствованного табличного решения «Tally», которое может оказаться полезным для повышения производительности при больших объемах исходных данных
Спасибо Ицхаку Хабинскому, поскольку его версия абсолютно верна и, вероятно, проще в обслуживании — я заблудился со своей версией XML, поэтому перешел к таблице подсчета. Просто предостережение от решения от Ицхака, если предоставлено больше показаний radius, чем предоставлено показаний часов — мы не узнаем об этом из результатов напрямую — вся запись исключается.
Вот мое (возможно, длинное) решение
Шаг 1) Создайте таблицу подсчета
Шаг 2) Создайте табличную функцию, которая связывает и преобразует значение с разделителями в таблицу с числом строк
Шаг 3 — Развертывание табличной функции, которая соответствующим образом анализирует строку и возвращает таблицу с преобразованными записями
Шаг 4 Тестовые примеры — и пример использования
Шаг 5 — Измените функцию main, чтобы скрыть все столбцы справа от столбца «Radius»
Шаг 1) Создайте таблицу подсчета из https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1
--=============================================================================
-- Setup Tally Table
--=============================================================================
USE TempDB --DB that everyone has where we can cause no harm
SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime DATETIME --Timer to measure total duration
SET @StartTime = GETDATE() --Start the timer
--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop and create the table/Primary Key
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
CREATE TABLE dbo.Tally
(N INT,
CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))
--===== Create and preset a loop counter
DECLARE @Counter INT
SET @Counter = 1
--===== Populate the table using the loop and couner
WHILE @Counter <= 11000
BEGIN
INSERT INTO dbo.Tally
(N)
VALUES (@Counter)
SET @Counter = @Counter 1
END
--===== Display the total duration
SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) ' Milliseconds duration'
Шаг 2) Создайте табличную функцию, которая связывает и преобразует значение с разделителями в таблицу с числом строк
Это гарантирует, что даже при наличии сотен показаний radius и clock — каждое из них будет быстро преобразовано в вложенную таблицу.
https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-“csv-splitter”-function
Функция использует таблицу подсчета, созданную на шаге 1
create function [dbo].[SplitAndSequence] (@pString nvarchar(4000), @pDelimiter char(1)) returns table return
SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY t.N),
Item = SUBSTRING(@pString, t.N, CHARINDEX(@pDelimiter, @pString @pDelimiter, t.N) - t.N)
FROM TempDB.dbo.Tally t
WHERE t.N <= DATALENGTH(@pString) 1 --DATATLENGTH allows for trailing space delimiters
AND SUBSTRING(@pDelimiter @pString, t.N, 1) = @pDelimiter
Шаг 3 — Развертывание табличной функции, которая соответствующим образом анализирует строку и возвращает таблицу с преобразованными записями
CREATE function [dbo].[fn_ParseAndReturnObservations] (@Emp_ID varchar(50), @WorkDateStr varchar(10), @String nvarchar(300)) returns table
-- select * from [fn_ParseAndReturnObservations]('1234', '12/10/2020', 'The following clocks 12:03,12:04 are outside of the allowed radius by 209759,209758 meters')
return
with [cte_SourceData] as (
select [Emp_ID] = @Emp_Id -- '1234'
, [Work Date] = convert(date,@WorkDateStr,103) -- convert(date,'12/10/2020',103)
, [String] = @String -- 'The following clocks 12:03,12:04 are outside of the allowed radius by 209759,209758 meters'
) -- end [cte_SourceData]
, [cte_Parse1] as (
select [Emp_ID], [Work Date]
, [Clocks Position] = charindex('clocks ', [String]) len('clocks')
, [String]
from [cte_SourceData]
) -- end [cte_Parse1]
, [cte_Parse2] as (
select [Emp_ID], [Work Date] , [Clocks Position]
, [String From Clock Observations] = substring([String],[Clocks Position] 1, len([String]))
, [String]
from [cte_Parse1]
) -- end [cte_Parse2]
, [cte_Parse3] as (
select [Emp_ID], [Work Date]
, [Clock Observations] = substring([String From Clock Observations],1, charindex(' ',[String From Clock Observations])-1)
, [Clocks Position], [String From Clock Observations]
, [Radius Verbosity Position Pre Ends] = charindex(' are outside of the allowed radius by ', [String From Clock Observations]) len(' are outside of the allowed radius by ')
--- Assumes no spaces between Clock Observations
, [String]
from [cte_Parse2]
) -- end [cte_Parse3]
, [cte_Parse4] as (
select [Emp_ID], [Work Date], [Clock Observations]
, [String From Meters] = substring([String From Clock Observations],[Radius Verbosity Position Pre Ends] 1,len([String From Clock Observations]))
, [Clocks Position] , [String From Clock Observations], [String]
from [cte_Parse3]
) -- end [cte_Parse4]
, [cte_ReadyForDelimiting] as (
select [Emp_ID], [Work Date], [Clock Observations]
, [Radius Observations] = substring([String From Meters],1, charindex(' meters', [String From Meters]))
-- , [String From Meters] , [Clocks Position] , [String From Clock Observations], [String]
from [cte_Parse4]
) -- end [cte_ReadyForDelimiting]
, [cte_CountNumberOfSubColumns] as (
select [rdy].[Emp_ID], [rdy].[Work Date], [rdy].[Clock Observations], [rdy].[Radius Observations]
, [Clock Observation Count] = (len([rdy].[Clock Observations]) - len(replace([rdy].[Clock Observations],',',''))) / (len([rdy].[Clock Observations]) - len(replace([rdy].[Clock Observations],';','')) 1) 1
, [Radius Observation Count] = (len([Radius Observations]) - len(replace([Radius Observations],',',''))) / (len([Radius Observations]) - len(replace([Radius Observations],';','')) 1) 1
from [cte_ReadyForDelimiting] [rdy]
) -- end [cte_CheckNumberOfColumns]
, [cte_ParsingClockObservations] as (
select [src].[Emp_ID], [src].[Work Date], [src].[Clock Observations], [src].[Radius Observations]
, [src].[Clock Observation Count] , [src].[Radius Observation Count]
, [Clock Record Sequence] = [Times].[ItemNumber]
, [Clock] = [Times].[Item]
from [cte_CountNumberOfSubColumns] [src]
outer apply (select * from [tempDB].[dbo].[SplitAndSequence] ([src].[Clock Observations],',')) [Times]
) -- [cte_ParsingClockObservations]
, [cte_ParsingRadiusObservations] as (
select [src].[Emp_ID], [src].[Work Date], [src].[Clock Observations], [src].[Radius Observations]
, [src].[Clock Observation Count] , [src].[Radius Observation Count]
, [Radius Record Sequence] = [Radius].[ItemNumber]
, [Radius] = [Radius].[Item]
from [cte_CountNumberOfSubColumns] [src]
outer apply (select * from [tempDB].[dbo].[SplitAndSequence] ([src].[Radius Observations],',')) [Radius] -- on [Radius].[ItemNumber] = [Times].[ItemNumber] */
) -- end [cte_ParsingRadiusObservations]
, [cte_TransposedRecords] as (
select [Emp_ID] = cast(isnull([times].[Emp_ID],[radius].[Emp_ID]) as varchar(10))
, [Work Date] = isnull([times].[Work Date],[radius].[Work Date])
, [Clock] = cast(case when isnull([times].[Clock],'N/A') in ('') then 'N/A' else isnull([times].[Clock],'N/A') end as varchar(10))
, [Radius] = cast(case when isnull([radius].[Radius], 'N/A') in ('') then 'N/A' else isnull([radius].[Radius],'N/A') end as varchar(20))
, [Stub] = 'StubStubStub'
, [times].[Clock Record Sequence]
, [radius].[Radius Record Sequence]
, [times].[Clock Observations]
, [times].[Radius Observations]
, [times].[Clock Observation Count]
, [times].[Radius Observation Count]
from [cte_ParsingClockObservations] [times]
full outer join [cte_ParsingRadiusObservations] [radius] on [radius].[Emp_ID] = [times].[Emp_ID] and [radius].[Work Date] = [times].[Work Date] and [radius].[Radius Record Sequence] = [times].[Clock Record Sequence]
)
-- Original
select * from [cte_TransposedRecords]
Запустите тест с этой строкой кода (используя исходный набор данных запроса)
select * from [fn_ParseAndReturnObservations]
('1234',
'12/10/2020',
'The following clocks 12:03,12:04 are outside of the allowed radius by 209759,209758 meters')
Emp_ID Work Date Clock Radius
---------- ---------- ---------- --------------------
1234 2020-10-12 12:03 209759
1234 2020-10-12 12:04 209758
Шаг 4 Тестовые примеры — и пример использования
Замените последние три строки тестовых примеров для рабочей таблицы, которую вы хотите профилировать, на тактовое время и радиус re
with cte_SampleTests as (
select TestCase = 'Original Case with a break down', [Test_Emp_ID] = '1234', [Test_WorkDateStr] = '12/10/2020', [Test_String] = 'The following clocks 12:03,12:04 are outside of the allowed radius by 209759,209758 meters'
union select TestCase = 'Three Readings', [Test_Emp_ID] = '1234', [Test_WorkDateStr] = '12/10/2020', [Test_String] = 'The following clocks 12:03,12:04,12:05 are outside of the allowed radius by 209759,209758,209757 meters'
union select TestCase = 'Forth Clock Position Listed but no value', [Test_Emp_ID] = '1234', [Test_WorkDateStr] = '12/10/2020', [Test_String] = 'The following clocks 12:03,12:04,12:05, are outside of the allowed radius by 209759,209758,209757,209755 meters'
union select TestCase = 'Four Radius and Three Clock Readings', [Test_Emp_ID] = '1234', [Test_WorkDateStr] = '12/10/2020', [Test_String] = 'The following clocks 12:03,12:04,12:05 are outside of the allowed radius by 209759,209758,209757,209755 meters'
union select TestCase = 'Four Clock Readings and 3 Radius Readings', [Test_Emp_ID] = '1234', [Test_WorkDateStr] = '12/10/2020', [Test_String] = 'The following clocks 12:03,12:04,12:05,12:06 are outside of the allowed radius by 209759,209758,209757 meters'
union select TestCase = 'Four Clock Readings and Fourth Radius Reading missing', [Test_Emp_ID] = '1234', [Test_WorkDateStr] = '12/10/2020', [Test_String] = 'The following clocks 12:03,12:04,12:05,12:06 are outside of the allowed radius by 209759,209758,209757, meters'
union select TestCase = 'Four Clock Readings and third Radius Reading missing', [Test_Emp_ID] = '1234', [Test_WorkDateStr] = '12/10/2020', [Test_String] = 'The following clocks 12:03,12:04,12:05,12:06 are outside of the allowed radius by 209759,209758,,209757 meters'
)
select cte_SampleTests.[TestCase], x.*
from [cte_SampleTests]
cross apply (select * from [dbo].[fn_ParseAndReturnObservations]([cte_SampleTests].[Test_Emp_ID], [cte_SampleTests].[Test_WorkDateStr], [cte_SampleTests].[Test_String])) x
TestCase Emp_ID Work Date Clock Radius
----------------------------------------------------- ---------- ---------- ---------- ------------------
Original Case with a break down 1234 2020-10-12 12:03 209759
Original Case with a break down 1234 2020-10-12 12:04 209758
Three Readings 1234 2020-10-12 12:03 209759
Three Readings 1234 2020-10-12 12:04 209758
Three Readings 1234 2020-10-12 12:05 209757
Forth Clock Position Listed but no value 1234 2020-10-12 12:03 209759
Forth Clock Position Listed but no value 1234 2020-10-12 12:04 209758
Forth Clock Position Listed but no value 1234 2020-10-12 12:05 209757
Forth Clock Position Listed but no value 1234 2020-10-12 N/A 209755
Four Radius and Three Clock Readings 1234 2020-10-12 12:03 209759
Four Radius and Three Clock Readings 1234 2020-10-12 12:04 209758
Four Radius and Three Clock Readings 1234 2020-10-12 12:05 209757
Four Radius and Three Clock Readings 1234 2020-10-12 N/A 209755
Four Clock Readings and 3 Radius Readings 1234 2020-10-12 12:03 209759
Four Clock Readings and 3 Radius Readings 1234 2020-10-12 12:04 209758
Four Clock Readings and 3 Radius Readings 1234 2020-10-12 12:05 209757
Four Clock Readings and 3 Radius Readings 1234 2020-10-12 12:06 N/A
Four Clock Readings and Fourth Radius Reading missing 1234 2020-10-12 12:03 209759
Four Clock Readings and Fourth Radius Reading missing 1234 2020-10-12 12:04 209758
Four Clock Readings and Fourth Radius Reading missing 1234 2020-10-12 12:05 209757
Four Clock Readings and Fourth Radius Reading missing 1234 2020-10-12 12:06 N/A
Four Clock Readings and third Radius Reading missing 1234 2020-10-12 12:03 209759
Four Clock Readings and third Radius Reading missing 1234 2020-10-12 12:04 209758
Four Clock Readings and third Radius Reading missing 1234 2020-10-12 12:05 N/A
Four Clock Readings and third Radius Reading missing 1234 2020-10-12 12:06 209757
Шаг 5 — Измените функцию main, чтобы скрыть все столбцы справа от столбца «Radius»
Столбцы Stub и all справа от Stub перечислены для отображения «работы» — Может оказаться полезным иметь количество наблюдений Radius и Clock для последующего анализа и профилирования решений [Последовательность записей часов] и [Последовательность записей Radius]
Шаг 6 — вернитесь к исходному автору данных … и попросите их предоставить вам альтернативную структуру — XML не должен быть сложным