Фильтрация вертикальных данных Autodesk Vault, получение самой новой записи для каждого чертежа

Использование Microsoft SQL Server Express Edition (64-разрядная версия) 10.0.550.0

Я пытаюсь извлечь данные с сервера Autodesk Vault. SQL, используемый для получения необходимых данных, слишком сложен для моего текущего уровня знаний, поэтому я пытаюсь сложить головоломку, используя кусочки из Google и StackOverflow в качестве кусочков. Используя отличный ответ, я смог перенести вертикальные данные в удобный горизонтальный формат.

В базе данных Autodesk Vault хранится информация о чертежах САПР (среди прочего). Основная вертикальная таблица dbo.Property содержит информацию обо всех различных версиях каждого чертежа САПР. Проблема, с которой я сейчас сталкиваюсь, заключается в том, что я получаю слишком много данных. Мне просто нужны данные из последней версии каждого чертежа САПР.

Вот мой SQL до сих пор

select 
    CreateDate,
    EntityID,
    PartNumber,
    CategoryName,
    [Subject],
    Title
from 
(
    select 
        EntityID,
        CreateDate,
        [53] as PartNumber,
        [28] as CategoryName,
        [42] as [Subject],
        [43] as Title
    from 
    (
        select
            p.Value, 
            p.PropertyDefID,
            p.EntityID,
            e.CreateDate
        from dbo.Property as p
        inner join dbo.Entity as e on p.EntityID = e.EntityId
        where p.PropertyDefID in(28, 42, 43, 53)
        and e.EntityClassID = 8
    ) t1
    pivot 
    (
        max(Value)
        for PropertyDefID in([28], [42], [43], [53])
    ) t2
) t3
where PartNumber is not null
and PartNumber != ''
and CategoryName = 'Drawing'
-- (1) additional condition
order by PartNumber, CreateDate desc

Где dbo.Property.Value относится к типу данных sql_variant. Приведенный выше запрос приводит к набору данных, подобному этому:

CreateDate | EntityID | PartNumber | CategoryName | Subject | Title 
---------------------------------------------------------------------
2016-01-01 |    59046 |      10001 | Drawing      | Xxxxx   | Yyyyy
2016-05-01 |    60137 |      10001 | Drawing      | Xxxxx   | Yyyyy
2016-08-01 |    62518 |      10001 | Drawing      | Xxxx    | Yyyyyy
2016-12-16 |    63007 |      10001 | Drawing      | Xxxxxx  | Yyyyyy
2016-01-01 |    45776 |      10002 | Drawing      | Zzzzz   | NULL  
2016-11-01 |    65011 |      10002 | Drawing      | Zzzzzz  | NULL  
...
(about 23000 rows)

У меня проблема в том, что я получаю все версии для каждого рисунка. В приведенном выше примере мне нужна только последняя версия для PartNumber=10001 от «2016-12-16» и т. д.

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

and (PartNumber, CreateDate) in 
(
    select PartNumber, max(CreateDate)
    from t3
    group by PartNumber 
)

Причина, по которой я помечаю этот вопрос как «поворот», хотя поворот уже сделан, заключается в том, что я подозреваю, что поворот вызывает у меня проблемы. Я просто еще не смог окунуться в этот поворотный материал, и мне серьезно не хватает навыков оптимизации SQL. Может быть, фильтрацию следует делать на внутреннем уровне?


sql-server pivot autodesk
person Tomas Eklund    schedule 16.12.2016    source источник
comment
Я полагаю, у вас есть веская причина не использовать Веб-сервисы? Из документации: › Прямой доступ к базе данных Vault и ее редактирование строго запрещены.   -  person Maxence    schedule 17.12.2016
comment
@Maxence Не уверен, является ли это уважительной причиной или нет, но мне дали RDP на сервер с открытым окном MSSQL SMS и попросили извлечь определенный набор данных из базы данных. Я не знал, что есть API. Я попытался найти в Интернете документацию, но обнаружил, что ее не хватает. Есть ли REST API? Нам нужно получить доступ к Vault из простого веб-приложения.   -  person Tomas Eklund    schedule 20.12.2016
comment
Вместе с продуктом поставляется SDK. Дополнительную информацию можно найти здесь   -  person Maxence    schedule 22.12.2016

Ответы (1)


arrow_upward
0
arrow_downward

Черпая вдохновение из комментария, предоставленного @Strawberry, я продолжал работать и настраивать, пока не получил то, что, кажется, работает. Мне пришлось использовать PIVOT внутри PIVOT, чтобы все это работало.

Редактировать. Сначала я использовал представления, но затем требования изменились, так как мне пришлось работать с пользователем базы данных только для чтения. К счастью, мне по-прежнему разрешалось создавать временные таблицы.

Это окончательный результат.

if object_id('tempdb.dbo.#Properties', 'U') is not null
    drop table #Properties

create table #Properties 
(
    PartNumber  nvarchar(max),
    [Subject]   nvarchar(max),
    Title       nvarchar(max),
    CreateDate  datetime
)

insert into #Properties
(
    PartNumber,
    [Subject],
    Title,
    CreateDate
)
select 
    convert(nvarchar(max), PartNumber),
    convert(nvarchar(max), [Subject]), 
    convert(nvarchar(max), Title),
    convert(datetime, CreateDate)
from 
(
    select 
        EntityID,
        CreateDate,
        [53] as PartNumber,
        [42] as [Subject],
        [43] as Title
    from 
    (
        select
            p.Value, 
            p.PropertyDefID,
            p.EntityID,
            e.CreateDate
        from dbo.Property as p
        inner join dbo.Entity as e on p.EntityID = e.EntityId
        where p.PropertyDefID in (42, 43, 53)
        and e.EntityClassID = 8
        and p.EntityID in
        (
            select 
                max(EntityID) as MaxEntityID
            from 
            (
                select 
                    EntityID,
                    [28] as CategoryName,
                    [53] as PartNumber
                from
                (
                    select
                        p.Value,
                        p.EntityID,
                        p.PropertyDefID
                    from dbo.Property as p
                    inner join dbo.Entity as e on p.EntityID = e.EntityId
                    where p.PropertyDefID in (28, 53)
                    and e.EntityClassID = 8 -- FileIteration
                ) as t1
                pivot
                (
                    max(Value)
                    for PropertyDefID in ([28], [53])
                ) as t2
            ) as t3
            where CategoryName = 'Drawing'
            group by PartNumber
        )
    ) as t4
    pivot 
    (
        max(Value)
        for PropertyDefID in ([42], [43], [53])
    ) as t5
) as t6
where PartNumber is not null
and PartNumber != ''
order by PartNumber

select * from #Properties;
-- search conditions goes here

Мне пришлось изменить предложенный join на where x in(y), потому что соединение было безумно медленным (я завершил запрос через четыре минуты). Теперь результирующий набор данных (на создание которого уходит около 2 секунд) выглядит многообещающе:

PartNumber | Subject | Title  | CreateDate       | ...
-----------------------------------------------------------------------
100000     | Xxxxxx  | Yyyyyy | 2015-08-17 09-10 | ...
100001     | Zzzzzz  | NULL   | 2015-09-02 15-23 | ...
...
(about 8900 rows)

Старых ревизий в наборе больше нет.

person Tomas Eklund    schedule 20.12.2016