Групповые символы поля varchar

Я создаю инструмент импорта данных от нескольких поставщиков. К сожалению, данные генерируются не мной, поэтому я должен работать с ними. Я столкнулся со следующей ситуацией.

У меня есть таблица, подобная следующей: 

ID    |SartDate    |Availability
========================================
H1    |20130728    |YYYYYYNNNNQQQQQ
H2    |20130728    |NNNNYYYYYYY
A3    |20130728    |NNQQQQNNNNNNNNYYYYYY
A2    |20130728    |NNNNNYYYYYYNNNNNN

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

  • Y: Доступно
  • N: Недоступно
  • Q : По Запросу

Например, для ID H1 20130728-20130802 доступен, затем с 20130803 - 20130806 недоступен и с 20130807-20130811 доступен по запросу.

Что мне нужно сделать, так это преобразовать эту таблицу в следующую настройку: 

ID    |Available   |SartDate    |EndDate     
========================================
H1    |Y           |20130728    |20130802    
H1    |N           |20130803    |20130806    
H1    |Q           |20130806    |20130811    
H2    |N           |20130728    |20130731
H2    |Y           |20130801    |20130807
A3    |N           |20130728    |20130729
A3    |Q           |20130730    |20130802
A3    |N           |20130803    |20130810
A3    |Y           |20130811    |20130816
A2    |Y           |20130728    |20130801
A2    |Y           |20130802    |20130807
A2    |Y           |20130808    |20130813
Начальная таблица содержит приблизительно 40 000 строк. Колонка доступности может иметь несколько дней (я видел до 800).

Я попытался превратить доступность в строки, а затем сгруппировать последовательные дни вместе, а затем получить мин и Макс дату для каждой группы. Для этого я использовал три или четыре CTE

Это прекрасно работает для нескольких идентификаторов, но когда я пытаюсь применить его ко всей таблице, это занимает годы (я остановил начальный тестовый запуск после дурацкого сна, и он не закончился, и да, я имею в виду, что я спал, пока он работал!!!!)

Я подсчитал, что если я переверну каждый символ в одной строке, то у меня получится что-то вроде 14,5 миллионов строк.

Итак, я спрашиваю, есть ли более эффективный способ сделать это? (Я знаю, что есть, но мне нужно, чтобы вы мне сказали)

Заранее благодарю.

4 2
sql sql-server sql-server-2008-r2 sql-server-express

4 ответа:

Это можно сделать в SQL Server, используя рекурсивные CTEs. Вот пример: 

with t as (
    select 'H1' as id, cast('20130728' as date) as StartDate,
           'YYYYYYNNNNQQQQQ' as Availability union all
    select 'H2' as id, cast('20130728' as date) as StartDate,
           'NNNNYYYYYYY' as Availability union all
    select 'H3' as id, cast('20130728' as date) as StartDate,
           'NQ' as Availability 
   ),
   cte as (
     select id, left(Availability, 1) as Available,
            StartDate as thedate,
            substring(Availability, 2, 1000) as RestAvailability,
            1 as i,
            1 as periodcnt
     from t
     union all
     select t.id, left(RestAvailability, 1),
            dateadd(dd, 1, thedate),
            substring(RestAvailability, 2, 1000) as RestAvailability,
            1 + cte.i,
            (case when substring(t.Availability, i, 1) = substring(t.Availability, i+1, 1)
                  then periodcnt
                  else periodcnt + 1
             end)
     from t join
          cte
          on t.id = cte.id
     where len(RestAvailability) > 0

   )
select id, min(thedate), max(thedate), Available
from cte
group by id, periodcnt, Available;

Способ, которым это работает, заключается в том, что он сначала распределяет даты. Это будет "типичное" использование обобщенных табличных выражений. В процессе он также отслеживает, изменилось ли Available от предыдущего значения (в переменной periodcnt. Для этого он использует струнные манипуляции.

С этой информацией конечный результат является просто агрегацией из этого CTE.

2

Поскольку SQL Server не является лучшим инструментом, если бы мне пришлось это сделать, я, вероятно, настроил бы пакет служб Integration Services, где я использовал бы компонент сценария для кодирования создания нескольких записей из одной в C#.

0

Вы пробовали использовать CROSS APPLY Может ли это дать лучшую производительность? Это не полный ответ. Просто еще один способ разбора?

Edit : теперь я использую table variable для индексной таблицы. 

DECLARE @MaxLen INT
SELECT @MaxLen = MAX(LEN(Availability))
FROM InputTable 
DECLARE @a TABLE (i int)

;WITH x AS
(
    SELECT 1 AS i
    UNION ALL SELECT i + 1 FROM x WHERE i <= @MaxLen
)
INSERT INTO @a 
SELECT i FROM x
OPTION (MAXRECURSION 0);


;WITH cte AS (
SELECT *, DATEADD(DAY, i-1, StartDate) StatusAtDay
FROM InputTable t
cross apply (
    select SUBSTRING(t.Availability, i, 1)  as c, i
    from @a
    WHERE LEN(Availability) >= i
    ) ca
)
SELECT *
FROM cte
order by 1

Я попробовал с некоторыми 5000 строк и где длина Availability > 1250 это заняло 19 секунд (бросая вывод в временную таблицу).

0

Я попробовал другой подход. Вместо того чтобы использовать библиотеку SQLXMLBulkLoad с исходным xml-файлом, я подумал, что могу выполнить преобразование с помощью LINQ, а затем массово загрузить выходные данные в БД.

Итак, мой исходный xml был примерно таким: 

<vacancies>
  <vacancy>
    <code>AT1010.200.1</code>
    <startday>2010-07-01</startday>
    <availability>YYYYYYNNNNQQQQQ</availability>
    <changeover>CCIIOOX</changeover>
    <minstay>GGGGGGGG</minstay>
    <flexbooking>YYYYY</flexbooking>
  </vacancy>
  <vacancy>
    <code>AT1010.200.2</code>
    <startday>2010-07-01</startday>
    <availability>NNNNYYYYYYY</availability>
    <changeover>CCIIOOX</changeover>
    <minstay>GGGGGGGG</minstay>
    <flexbooking>YYYYY</flexbooking>
  </vacancy>
  <vacancy>
    <code>AT1010.200.3</code>
    <startday>2010-07-01</startday>
    <availability>NNQQQQNNNNNNNNYYYYYY</availability>
    <changeover>CCIIOOX</changeover>
    <minstay>GGGGGGGG</minstay>
    <flexbooking>YYYYY</flexbooking>
  </vacancy>
  <vacancy>
    <code>AT1010.200.4</code>
    <startday>2010-07-01</startday>
    <availability>NNNNNYYYYYYNNNNNN</availability>
    <changeover>CCIIOOX</changeover>
    <minstay>GGGGGGGG</minstay>
    <flexbooking>YYYYY</flexbooking>
  </vacancy>
</vacancies>

Задача здесь будет состоять в создании нового xml, который будет иметь даты начала и окончания для каждой группы флагов доступности.

XElement xe = XElement.Load(file);

int i = 0;
char previousFlag = ' ';
int GroupIndex = 0;

XElement vacancies =
new XElement
(
    "vacancies",
    xe.Elements("vacancy")
    .Select
    (
        x =>
        {
            i = 0;
            GroupIndex = 0;
            return new
            {
                availabilities = x.Element("availability")
                .Value
                .Select
                (
                    v =>
                    {
                        if (previousFlag != v)
                        {
                            GroupIndex++;
                        }
                        previousFlag = v;
                        return new
                        {
                            Code = x.Element("code").Value,
                            startday = x.Element("startday").Value,
                            Date = DateTime.Parse(x.Element("startday").Value).AddDays(i++),
                            GIndex = GroupIndex
                        };
                    }
                )
            };
        }
    )
    .SelectMany
    (
        x =>
        x.availabilities
    )
    .GroupBy
    (
        g =>
        new
        {
            Code = g.Code,
            startday = g.startday,
            GroupIndex = g.GIndex
        }
    )
    .Select
    (
        x =>
        new XElement
        (
            "vacancy",
            new XElement("code", x.Key.Code),
            new XElement("startday", x.Key.startday),
            new XElement("GroupIndex", x.Key.GroupIndex),
            new XElement("minDate", x.Min(z => z.Date)),
            new XElement("maxDate", x.Max(z => z.Date))
        )
    )
);
vacancies.Save(outputfile);

Открывая выходной файл у меня есть следующий формат xml: 

<vacancies>
  <vacancy>
    <code>AT1010.200.1</code>
    <startday>2010-07-01</startday>
    <GroupIndex>1</GroupIndex>
    <minDate>2010-07-01T00:00:00</minDate>
    <maxDate>2010-07-06T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.1</code>
    <startday>2010-07-01</startday>
    <GroupIndex>2</GroupIndex>
    <minDate>2010-07-07T00:00:00</minDate>
    <maxDate>2010-07-10T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.1</code>
    <startday>2010-07-01</startday>
    <GroupIndex>3</GroupIndex>
    <minDate>2010-07-11T00:00:00</minDate>
    <maxDate>2010-07-15T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.2</code>
    <startday>2010-07-01</startday>
    <GroupIndex>1</GroupIndex>
    <minDate>2010-07-01T00:00:00</minDate>
    <maxDate>2010-07-04T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.2</code>
    <startday>2010-07-01</startday>
    <GroupIndex>2</GroupIndex>
    <minDate>2010-07-05T00:00:00</minDate>
    <maxDate>2010-07-11T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.3</code>
    <startday>2010-07-01</startday>
    <GroupIndex>1</GroupIndex>
    <minDate>2010-07-01T00:00:00</minDate>
    <maxDate>2010-07-02T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.3</code>
    <startday>2010-07-01</startday>
    <GroupIndex>2</GroupIndex>
    <minDate>2010-07-03T00:00:00</minDate>
    <maxDate>2010-07-06T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.3</code>
    <startday>2010-07-01</startday>
    <GroupIndex>3</GroupIndex>
    <minDate>2010-07-07T00:00:00</minDate>
    <maxDate>2010-07-14T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.3</code>
    <startday>2010-07-01</startday>
    <GroupIndex>4</GroupIndex>
    <minDate>2010-07-15T00:00:00</minDate>
    <maxDate>2010-07-20T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.4</code>
    <startday>2010-07-01</startday>
    <GroupIndex>1</GroupIndex>
    <minDate>2010-07-01T00:00:00</minDate>
    <maxDate>2010-07-05T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.4</code>
    <startday>2010-07-01</startday>
    <GroupIndex>2</GroupIndex>
    <minDate>2010-07-06T00:00:00</minDate>
    <maxDate>2010-07-11T00:00:00</maxDate>
  </vacancy>
  <vacancy>
    <code>AT1010.200.4</code>
    <startday>2010-07-01</startday>
    <GroupIndex>3</GroupIndex>
    <minDate>2010-07-12T00:00:00</minDate>
    <maxDate>2010-07-17T00:00:00</maxDate>
  </vacancy>
</vacancies>

Которая плоская и готов к обработке инструментом SQLXMLBulkLoad без необходимости дальнейшего процесса.

Мой исходный xml был 60 МБ, и он был преобразован в файл 45 МБ менее чем за одну минуту, и хотя я не тестировал SQLXMLBulkLoad в новом файле, это будет молниеносно, так как я знаю его производительность с исходным файлом.

Я все равно буду пробовать все ваши решения, так как вы, безусловно, стоите попробовать, и я приму лучшее из них.

Спасибо всем за усилия.

0