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

Откуда:
Сообщений: 9
Всем привет!

Есть задача агрегировать (назовем это так) данные нескольких строк.
Например, есть у меня 3 записи, содержащие информацию о каком-то пользователе.
Мне нужно взять строку с самыми последними данными, и дополнить ее недостающими из "предыдущих" строк.

Например:

id last   first middle   bd         bplace phone          actuality
-- ------ ----- -------- ---------- ------ -------------- ----------
 1 Иванов Иван  NULL     05.03.1981 NULL   8-985-123-4567 27.03.2015
 2 Иванов Иван  Иванович 05.03.1981 NULL   8-985-123-2222 15.02.2015
 3 Иванов Иван  Петрович            Москва 8-985-123-1111 01.01.2013

result
   Иванов Иван  Иванович 05.03.1981 Москва 8-985-123-4567 27.03.2015


Как сделать курсорами знаю. Может есть более идеологически правильные пути? Задача-то не редкая, думаю..
10 апр 15, 10:32    [17497187]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
f095
Мне нужно взять строку с самыми последними данными

А "последние" - это какие ? От чего отсчитывать последовательность ?
10 апр 15, 10:33    [17497199]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
f095,

Таким образом, Иванов Иван Иванович выселил Иванова Ивана Петровича
и занял его жилплощадь? Круто!
10 апр 15, 10:35    [17497208]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
f095
Member

Откуда:
Сообщений: 9
Glory
А "последние" - это какие ? От чего отсчитывать последовательность ?

Столбец actuality - дата актуальности данных. Соответственно если дата самая последняя - данные самые актуальные.
10 апр 15, 10:40    [17497242]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
ЦБ
Member [заблокирован]

Откуда:
Сообщений: 2773
f095,

А какой признак, что эти записи относятся к одному человеку?
10 апр 15, 10:43    [17497260]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
f095
Member

Откуда:
Сообщений: 9
ЦБ
А какой признак, что эти записи относятся к одному человеку?

f095
есть у меня 3 записи, содержащие информацию о каком-то пользователе

Постановка задачи такая. Если очень нужно, пусть у них у всех будет одинаковый 'human-id' равный 3-м и не включенный в select выше.
10 апр 15, 10:59    [17497351]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
f095
Столбец actuality - дата актуальности данных. Соответственно если дата самая последняя - данные самые актуальные.

И что, если записей будет больше 3х ?
Для каждого NULL-го поля записи с наибольшим actuality нужно искать запись с заполненным полем и наиболее поздней датой actuality ?
10 апр 15, 11:05    [17497389]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4478
f095
Всем привет!

Есть задача агрегировать (назовем это так) данные нескольких строк.
Например, есть у меня 3 записи, содержащие информацию о каком-то пользователе.
Мне нужно взять строку с самыми последними данными, и дополнить ее недостающими из "предыдущих" строк.

Например:

id last   first middle   bd         bplace phone          actuality
-- ------ ----- -------- ---------- ------ -------------- ----------
 1 Иванов Иван  NULL     05.03.1981 NULL   8-985-123-4567 27.03.2015
 2 Иванов Иван  Иванович 05.03.1981 NULL   8-985-123-2222 15.02.2015
 3 Иванов Иван  Петрович            Москва 8-985-123-1111 01.01.2013

result
   Иванов Иван  Иванович 05.03.1981 Москва 8-985-123-4567 27.03.2015

Как-то так:
(SELECT TOP 1 last FROM table ORDER BY actuality WHERE NULLIF(last,'') IS NOT NULL) as last,
(SELECT TOP 1 first FROM table ORDER BY actuality WHERE NULLIF(first ,'') IS NOT NULL) as first ,
(SELECT TOP 1 middle   FROM table ORDER BY actuality WHERE NULLIF(middle   ,'') IS NOT NULL) as middle   ,
(SELECT TOP 1 bd         FROM table ORDER BY actuality WHERE NULLIF(bd         ,'') IS NOT NULL) as bd,
(SELECT TOP 1 bplace FROM table ORDER BY actuality WHERE NULLIF(bplace,'') IS NOT NULL) as bplace,
(SELECT TOP 1 phone FROM table ORDER BY actuality WHERE NULLIF(phone,'') IS NOT NULL) as phone
10 апр 15, 11:11    [17497432]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
f095
Member

Откуда:
Сообщений: 9
Glory
И что, если записей будет больше 3х ?
Для каждого NULL-го поля записи с наибольшим actuality нужно искать запись с заполненным полем и наиболее поздней датой actuality ?

Именно.

SQL2008, похоже на правду. Нужно будет по скорости с "курсорной" реализацией сравнить.
10 апр 15, 11:16    [17497466]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
gang
Member

Откуда:
Сообщений: 1394
f095
Задача-то не редкая, думаю..

К сожалению да, халтура нынче не редкость.
Без курсоров сделать можно, но все равно будет "не по феншую".
По уму, лучше бы контролировать наличие всех данных на стадии записи новой строки.
Чесать правой ухо левой пяткой можно примерно так:
1. Найти всех первых в группах с одинаковым human-id, в том порядке который Вы сочтете правильным.
Вы пишите что актуальность определяется по actuality, но не понятно как у вас строка с "лучшей актуальностью" по дате
имеет наименьший Id. Это странно, но Вам видней. Для ранжирования как водится нужна функция row_number() over (partition by human-id order by order_field)
2. Затем нужно оставить только тех у кого результат row_number()=1 и для каждой колонки написать
isnull (myfiled, (select top 1 myfiled from mytable as innertab where innertab.human-id=outerTab.innertab and myfiled is not null order by order_field) )
Если будет много данных и не получится навесить правильные индексы, будет работать весьма неспешно.
Так что лучше все таки лечить логику, а не прикручивать костыли.
10 апр 15, 11:21    [17497508]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4478
f095
Glory
И что, если записей будет больше 3х ?
Для каждого NULL-го поля записи с наибольшим actuality нужно искать запись с заполненным полем и наиболее поздней датой actuality ?

Именно.

SQL2008, похоже на правду. Нужно будет по скорости с "курсорной" реализацией сравнить.

Вообще такие запросы работают ужасно долго.
Можно попробовать через CTE оптимизировать.
10 апр 15, 11:26    [17497560]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4478
Ну а разумеется нужно будет включить отбор по клиенту
(SELECT TOP 1 last FROM table ORDER BY actuality WHERE NULLIF(last,'') IS NOT NULL   AND table.human-id = t.human-id) as last,

...

FROM table t
10 апр 15, 11:34    [17497642]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
f095
Member

Откуда:
Сообщений: 9
gang, дело в том, что данные приходят из разных организаций и могут быть не полными. Я не могу заставить их присылать абсолютно всю информацию, они присылают только то, что известно им самим (и вероятнее всего значимы именно для их бизнеса). Причем дата актуальности данных не сильно связана с временем прихода этих данных в БД, поэтому ID это просто ID (более поздний приход данных не означает их большую актуальность).
Отдавать же мне нужно всю полученную информацию, причем с возможностью выгрузки "на дату" (типа "на такую-то дату у нас была такая вот информация"), откидывая более новую инфу.

Полей, естественно, сильно больше чем в примере и табличка не одна. Получается, "по феншую", красиво не получится?
10 апр 15, 11:46    [17497737]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
f095
Получается, "по феншую", красиво не получится?

" контролировать наличие всех данных на стадии записи новой строки", т.е. когда "данные приходят из разных организаций"
Т.е. не должно быть 3х записей в данном случае, должна быть 1 запись
10 апр 15, 11:49    [17497761]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
ЦБ
Member [заблокирован]

Откуда:
Сообщений: 2773
f095,

Если "в лоб", то так (в принципе, это тоже, что выше уже предложили)

declare @t table (human_id int, last varchar(50), first varchar(50), middle varchar(50),   bd datetime, bplace  varchar(50), phone varchar(50), actuality datetime)
set dateformat dmy

insert into @t

select 3, 'Иванов', 'Иван',  NULL,     '05.03.1981', NULL   , '8-985-123-4567', '27.03.2015' union all
select 3, 'Иванов', 'Иван',  'Иванович', '05.03.1981', NULL   , '8-985-123-2222', '15.02.2015' union all
select 3, 'Иванов', 'Иван',  'Петрович',  null,          'Москва', '8-985-123-1111', '01.01.2013'

-- select * from @t

select a.human_id, t1.last, t2.first, t3.middle, t4.bd, t5.bplace, t6.phone, max(a.actuality) as actuality 

from @t a
outer apply (select top 1 b.last from @t b where a.human_id=b.human_id and b.last is not null order by actuality desc) t1
outer apply (select top 1 b.first from @t b where a.human_id=b.human_id and b.first is not null order by actuality desc) t2
outer apply (select top 1 b.middle from @t b where a.human_id=b.human_id and b.middle is not null order by actuality desc) t3
outer apply (select top 1 b.bd from @t b where a.human_id=b.human_id and b.bd is not null order by actuality desc) t4
outer apply (select top 1 b.bplace from @t b where a.human_id=b.human_id and b.bplace is not null order by actuality desc) t5
outer apply (select top 1 b.phone from @t b where a.human_id=b.human_id and b.phone is not null order by actuality desc) t6

group by a.human_id, t1.last, t2.first, t3.middle, t4.bd, t5.bplace, t6.phone


но с учётом этого
f095
Полей, естественно, сильно больше чем в примере

вот такой "феншуй" может оказаться получшеее :)
declare @xml xml

set @xml = (select * from @t for xml raw, elements, type)

;with cte as 
(
	select 
			 t.c.value('../human_id[1]/text()[1]', 'int') as human_id
			,t.c.value('../actuality[1]/text()[1]', 'datetime') as actuality
			,a.fieldname
			,a.fieldvalue
	from @xml.nodes('/row/*') as t(c)
	cross apply 
			(select 
				 t.c.value('local-name(.)', 'varchar(50)') as fieldname
				,t.c.value('./text()[1]', 'varchar(50)') as fieldvalue
			 where t.c.value('local-name(.)', 'varchar(20)') not in ('human_id', 'actuality')) a
),
cte2 as
(
	select human_id, fieldname, fieldvalue, row_number()over(partition by human_id, fieldname order by actuality desc) as rn,
		max(actuality)over(partition by human_id) as actuality
	from cte
),
cte3 as
(
	select * from cte2 where rn=1
)

select human_id, last, first, middle, bd, bplace, phone, actuality from cte3
pivot (max(fieldvalue) for fieldname in (last, first, middle, bd, bplace, phone)) as pvt
10 апр 15, 11:56    [17497804]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
gang
Member

Откуда:
Сообщений: 1394
Glory
f095
Получается, "по феншую", красиво не получится?

" контролировать наличие всех данных на стадии записи новой строки", т.е. когда "данные приходят из разных организаций"
Т.е. не должно быть 3х записей в данном случае, должна быть 1 запись

+1. Зачем эта история доставки неполных данных? Приходит новая срока, сравниваете актуальность, мержите. Если уж сильно хочется, отдельно сохраняете лог.
10 апр 15, 11:57    [17497814]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SQL2008
Ну а разумеется нужно будет включить отбор по клиенту
(SELECT TOP 1 last FROM table ORDER BY actuality WHERE NULLIF(last,'') IS NOT NULL   AND table.human-id = t.human-id) as last,

...

FROM table t
Чо за ерунда? Какой-такой NULLIF? Чем last <> '' не устраивает?
10 апр 15, 12:08    [17497905]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
ЦБ
Member [заблокирован]

Откуда:
Сообщений: 2773
iap
Чо за ерунда? Какой-такой NULLIF? Чем last <> '' не устраивает?
что бы "отсечь" и НУЛЛ и "пустую строку" одним условием, имхо
10 апр 15, 12:10    [17497917]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
f095
Member

Откуда:
Сообщений: 9
gang, оно так и происходит в основном.
Все это нужно, когда народ хочет получить данные "по состоянию на неделю назад". Тут мы не можем взять суммарные данные, т. к. часть из них еще не была известна на тот момент. Следовательно ограничиваем actuality < '20150402' (например) и вычисляем все по имеющимся данным.
Или еще, например, какая-то из организаций может осознать что она хрень прислала и прислать комманду "аннулирования". Нужно будет вернуть "агрегированную" информацию на момент, до "обновления хренью" :)

Ну в общем, подход ясен. Буду пробовать.
10 апр 15, 12:12    [17497932]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
f095
оно так и происходит в основном.

Что значит "так и приходит" ?
Сразу вот так 3 записи в одной транзакции ?
10 апр 15, 12:14    [17497944]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
ЦБ
iap
Чо за ерунда? Какой-такой NULLIF? Чем last <> '' не устраивает?
что бы "отсечь" и НУЛЛ и "пустую строку" одним условием, имхо
А то, что я написал, не отсекает разве?
Но в то же время SARGable!
10 апр 15, 12:20    [17497994]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
f095
Member

Откуда:
Сообщений: 9
Glory, нет.

Есть таблица event'ов. В нее сохраняется все что приходит в том виде, в каком приходит. Это соответствует тем 3-ём Ивановым из первого поста.
Есть таблица агрегированных данных. Туда мерджим данные по последней пришедшей записи. Содержит все что известно. Соответствует тому одному Иванову в result'е.

Для получения текущих данных используется таблица агрегатов.

Если нужно получить "на дату", или "откатить" агрегированные данные - тогда по таблице event'ов восстанавливаем все что было на тот момент.
10 апр 15, 12:40    [17498128]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
f095
Есть таблица event'ов. В нее сохраняется все что приходит в том виде, в каком приходит. Это соответствует тем 3-ём Ивановым из первого поста.
Есть таблица агрегированных данных. Туда мерджим данные по последней пришедшей записи. Содержит все что известно. Соответствует тому одному Иванову в result'е.

И почему сразу не сохранять всю историю мерджей, а не только конечный мердж ?
10 апр 15, 12:51    [17498203]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
f095
Member

Откуда:
Сообщений: 9
Glory, придет инфа из 10 мест, потом первый из них отмену пришлет. Придется брать предыдущий "живой" мердж из истории мерджей и накатывать последовательно 9 евентов?
Сейчас берется последний евент и дополняется предыдущими. Не думаю, что сильно хуже.
10 апр 15, 17:51    [17500002]     Ответить | Цитировать Сообщить модератору
 Re: Агрегирование данных  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
ЦБ
f095,

Если "в лоб", то так (в принципе, это тоже, что выше уже предложили)


А если не совсем в лоб, то я бы поступил вот так.

declare @t table (human_id int, last varchar(50), first varchar(50), middle varchar(50),   bd datetime, bplace  varchar(50), phone varchar(50), actuality datetime)
set dateformat dmy

insert into @t

select 3, 'Иванов', 'Иван',  NULL,     '05.03.1981', NULL   , '8-985-123-4567', '27.03.2015' union all
select 3, 'Иванов', 'Иван',  'Иванович', '05.03.1981', NULL   , '8-985-123-2222', '15.02.2015' union all
select 3, 'Иванов', 'Иван',  'Петрович',  null,          'Москва', '8-985-123-1111', '01.01.2013'

-- select * from @t

SELECT * FROM 
(
	select a.human_id, 
		last = ISNULL(a.last, t1.last), 
		first= ISNULL(a.first, t2.first), 
		middle= ISNULL(a.middle, t3.middle), 
		bd= ISNULL(a.bd, t4.bd), 
		bplace= ISNULL(a.bplace, t5.bplace), 
		phone= ISNULL(a.phone, t6.phone), 
		a.actuality,
		rn = ROW_NUMBER() OVER (PARTITION BY human_id ORDER BY actuality desc) 
	from @t a
	outer apply (select top 1 b.last from @t b where a.human_id=b.human_id and b.last is not null order by actuality desc) t1
	outer apply (select top 1 b.first from @t b where a.human_id=b.human_id and b.first is not null order by actuality desc) t2
	outer apply (select top 1 b.middle from @t b where a.human_id=b.human_id and b.middle is not null order by actuality desc) t3
	outer apply (select top 1 b.bd from @t b where a.human_id=b.human_id and b.bd is not null order by actuality desc) t4
	outer apply (select top 1 b.bplace from @t b where a.human_id=b.human_id and b.bplace is not null order by actuality desc) t5
	outer apply (select top 1 b.phone from @t b where a.human_id=b.human_id and b.phone is not null order by actuality desc) t6
) A
WHERE RN = 1


Жаль нет оконной Last_not_empty.
10 апр 15, 19:28    [17500407]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить