Пронумеровать в порядке возрастания

bmp2006

Такая задача:есть таблица t1 из полей 1. sys_num - уникальное int2. num - int 3. open_date - дата4. account - intпример записей|1|1|01.03.2005|29||2|3|01.03.2005|29||3|5|01.03.2005|33||4|1|01.03.2005|33||5|3|01.03.2005|33||6|2|02.03.2005|29||7|3|02.03.2005|29||8|5|02.03.2005|29|Требуется изменить значения num таким образом чтобы они для каждой даты (для каждого нового дня) начинались с 1 и шли по порядку 1,2,3.. Чтобы в итоге было вот так:|1|1|01.03.2005|29||2|2|01.03.2005|29||3|1|01.03.2005|33||4|2|01.03.2005|33||5|3|01.03.2005|33||6|1|02.03.2005|29||7|2|02.03.2005|29||8|3|02.03.2005|29|Как это можно реализовать запросом?
21 ответ

bmp2006

DENSE_RANK()


bmp2006

Чё-то меня колбасит 8)ROW_NUMBER


bmp2006

@@version ?For 2000: local FAQ


bmp2006

ROW_NUMBER - это функция MS SQL? мне нужно реализовать это средствами SQL. Например сначала обнулить все значения, затем выбирая поблочно по критерию ДАТА присваивать update'ом значения max+1. проблема именно с построением запроса. помогите пожалуйста


bmp2006

create table #t (id int ,depart char, name char)

insert #t select <b>1</b>,'a','b'

insert #t select <b>2</b>,'a','c'

insert #t select <b>3</b>,'b','e'

insert #t select <b>4</b>,'b','d'



select t2.id,t2.depart,t2.name,count(*)

from #t t1

 inner join #t t2

on t1.depart=t2.depart and 

 t1.id < = t2.id 

group by t2.id,t2.depart,t2.name
Спасибо, но мне нужно изменить записи в таблице. как это сделать?


bmp2006

Спасибо, но мне нужно изменить записи в таблице. как это сделать?
М.б. UPDATE?Уточни, что именно нужно


bmp2006

Чё-то меня колбасит 8)ROW_NUMBER
ROW_NUMBER появилась только в 2005


bmp2006

Задача не тривиальная, ради спортивного интереса:
set nocount on

-- Создал временную таблицу для отбора уникальных дат
declare @tmp table (dt_id int identity, dt_value varchar(<b>20</b>))

-- Создал временную таблицу для отбора sys_num по дате
declare @tmp_range table (dt_id int)

-- Заполнил таблицу уникальными датами
-- Смените 'test' на имя вашей таблицы!!!
insert @tmp select distinct convert(varchar(<b>20</b>), open_date, <b>4</b>) from test


declare @cur_date int
declare @cur_value varchar(<b>20</b>)
declare @upd_id int
declare @upd_cnt int

-- первый цикл - перебор уникальных дат
while (select count(*) from @tmp ) > <b>0</b>
begin

-- очистка временной таблицы с sys_num
delete @tmp_range
-- Сброс счетчика
set @upd_cnt = <b>1</b>

-- Выбор текущей даты
select @cur_date = dt_id, @cur_value = dt_value from @tmp where dt_id = (select min(dt_id) from @tmp)

-- Выбор всех sys_num которые соотвествуют дате
-- Смените 'test' на имя вашей таблицы!!!
insert @tmp_range select sys_num from test where convert(varchar(<b>20</b>), open_date, <b>4</b>) = @cur_value

-- второй цикл - обновление num
	while (select count(*) from @tmp_range) > <b>0</b>
	begin
		
		select @upd_id = dt_id from @tmp_range where dt_id = (select min(dt_id) from @tmp_range)
		-- Смените 'test' на имя вашей таблицы!!!
		update test set num = @upd_cnt where sys_num = @upd_id
		delete @tmp_range where dt_id = @upd_id
		set @upd_cnt = @upd_cnt + <b>1</b>
	end

delete @tmp where dt_id = @cur_date

end

select * from test
результат:1 1 2005-03-01 00:00:00.000 292 2 2005-03-01 00:00:00.000 293 3 2005-03-01 00:00:00.000 334 4 2005-03-01 00:00:00.000 335 5 2005-03-01 00:00:00.000 336 1 2005-03-02 00:00:00.000 297 2 2005-03-02 00:00:00.000 298 3 2005-03-02 00:00:00.000 29


bmp2006

результат:1 1 2005-03-01 00:00:00.000 292 2 2005-03-01 00:00:00.000 293 3 2005-03-01 00:00:00.000 334 4 2005-03-01 00:00:00.000 335 5 2005-03-01 00:00:00.000 336 1 2005-03-02 00:00:00.000 297 2 2005-03-02 00:00:00.000 298 3 2005-03-02 00:00:00.000 29
Причем нули в в временной части open_date у меня были изначально, скрипт, не изменяет эту часть open_date


bmp2006

drop table #t
drop table #t2

create table #t (sys_num int,num int, open_date datetime, account int)

insert into #t (sys_num ,num , open_date, account ) select <b>1</b>,<b>1</b>,'20050301',<b>29</b>
insert into #t (sys_num ,num , open_date, account ) select <b>2</b>,<b>3</b>,'20050301',<b>29</b>
insert into #t (sys_num ,num , open_date, account ) select <b>3</b>,<b>5</b>,'20050301',<b>33</b>
insert into #t (sys_num ,num , open_date, account ) select <b>4</b>,<b>1</b>,'20050301',<b>33</b>
insert into #t (sys_num ,num , open_date, account ) select <b>5</b>,<b>3</b>,'20050301',<b>33</b>
insert into #t (sys_num ,num , open_date, account ) select <b>6</b>,<b>2</b>,'20050302',<b>29</b>
insert into #t (sys_num ,num , open_date, account ) select <b>7</b>,<b>3</b>,'20050302',<b>29</b>
insert into #t (sys_num ,num , open_date, account ) select <b>8</b>,<b>5</b>,'20050302',<b>29</b>


select t2.sys_num,t2.open_date,t2.account, num = count(*)
into #t2
from #t t1 inner join 
	#t t2 on t2.open_date=t1.open_date and t2.account = t1.account
			and t1.sys_num < = t2.sys_num 
group by t2.sys_num,t2.open_date,t2.account


update t1
set num = t2.num
from #t t1 inner join 
	#t2 t2 on t1.sys_num = t2.sys_num 

select *
from #t


bmp2006

Требуется изменить значения num таким образом чтобы они для каждой даты (для каждого нового дня) начинались с 1 и шли по порядку 1,2,3.. Чтобы в итоге было вот так:|1|1|01.03.2005|29||2|2|01.03.2005|29||3|1|01.03.2005|33||4|2|01.03.2005|33||5|3|01.03.2005|33||6|1|02.03.2005|29||7|2|02.03.2005|29||8|3|02.03.2005|29|
Автор неккоректно указал в примере последовательность, судя по логике описания выделеные числа должны быть "3, 4, 5"


bmp2006

Все корректно.У него группировка не только по дате, но и по аккаунту.Не подгоняйте данные под свое решение ;)


bmp2006

Все корректно.У него группировка не только по дате, но и по аккаунту.Не подгоняйте данные под свое решение ;)
Требуется изменить значения num таким образом чтобы они для каждой даты (для каждого нового дня) начинались с 1 и шли по порядку 1,2,3.Руководствовался только постановкой задачи ;)


bmp2006

2Taffy:Признаться, Ваше решение более елегантно. Разве что, для оптимизации, временные таблицы можно сменить на table переменные.


bmp2006

2Taffy:Признаться, Ваше решение более елегантно. Разве что, для оптимизации, временные таблицы можно сменить на table переменные.
Оптимизации .... чего ?


bmp2006

Оптимизации .... чего ?
Учить матчасть! При использовании временных таблиц, используется tempdb, у которой хоть и более высокое быстродействие за счет упрощенного логирования, но все же временные таблицы физически создаются, вовлекая дисковую подсистему и механизм логирования tempdbТаблицы - переменные располагаются в памяти, они доступны только в контексте транзакции, батча. При создании, использовании таблиц-переменных логирование не производитьсяВыдержка из BOL
Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits: A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared. Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure 

SELECT select_list INTO table_variable statements.
table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.


bmp2006

Учить матчасть! При использовании временных таблиц, используется tempdb, у которой хоть и более высокое быстродействие за счет упрощенного логирования, но все же временные таблицы физически создаются, вовлекая дисковую подсистему и механизм логирования tempdbТаблицы - переменные располагаются в памяти, они доступны только в контексте транзакции, батча. При создании, использовании таблиц-переменных логирование не производитьсяВыдержка из BOL
Если речь идет об оптимизации ( о которой, кстати, автор топика не спрашивал ) , то тот же самый BOL ( + sql.ru *)) )BOL 2005"Запросы, содержащие табличные переменные, не создают параллельные запросы планов выполнения.Производительность может снижаться при наличии очень больших табличных переменных или при наличии табличных переменных в сложных запросах. В подобных случаях целесообразно рассмотреть возможность использования временных таблиц." Table переменные не имеют статистики , и, следовательно, оптимизатор не способен оптимизировать запрос к такой таблице.На table переменных нельзя явно создавать индексы - "подсказывание" ему индексов периодически составляет большую проблему.Использование табличных переменных позволит уменьшить число рекомпиляций - спорный момент приведет ли это к "оптимизации" ( имеется время исполнения самой SP )Может, что то забыл... Большая просьба добавить\поправить\тыкнуть на ошибочное суждение ( для ясности вопроса )________________________________________________________________Из всего вышесказанного НЕ значит, что table переменные нельзя использовать - это значит IMHO , что фраза "Разве что, для оптимизации, временные таблицы можно сменить на table переменные" - несколько пространна . IMHO, использование table переменных зависит от конкретной задачи и данных.________________________________________________________________PS По моим личным наблюдениям, table переменные следует использовать для не больших объемов данных (~ до 2 тыс записей)... Дальше лучше переходить на временные..


bmp2006

спасибо Вам всем большое. только из-за того что я пользую не MS SQL у меня не получается использовать временные таблицы... Хотелось бы подогнать это под себя, но не очень понятно.Для чего таблицы #t2 и #t1 и чем они заполнены изначально? Для чего условие t1.sys_num <=t2.sys_num
select t2.sys_num,t2.open_date,t2.account, num = count(*) into #t2 from #t t1 inner join #t t2 on t2.open_date=t1.open_date and t2.account = t1.account and t1.sys_num <=t2.sys_num group by t2.sys_num,t2.open_date,t2.account


bmp2006

только из-за того что я пользую не MS SQL у меня не получается использовать временные таблицы...
Тогда почему бы Вам не задать вопрос в соответствующем форуме?


bmp2006

Не буду углубляться в теорию. Практика:
set nocount on
declare @test table (dt_id int identity, dt_varchar varchar(<b>20</b>), dt_date datetime, dt_num int)

declare @cnt int
declare @cnt_offset int
set @cnt_offset = <b>1</b>
set @cnt = <b>1</b>


while (select @cnt) < <b>30000</b>
begin
if (@cnt_offset > <b>20</b>)
set @cnt_offset = <b>1</b>

insert into @test values(replicate(char(@cnt_offset+<b>100</b>), <b>15</b>), dateadd(d, @cnt_offset, getdate()), @cnt_offset ) 
set @cnt = @cnt + <b>1</b>
set @cnt_offset = @cnt_offset + <b>1</b>
end

update @test set dt_num = <b>100</b> where dt_num = <b>10</b>
select * from @test
Время выполнения: 1566 msУвеличение счетчика до 300 000 = 15321 ms
set nocount on
create table #test 
(dt_id int identity, dt_varchar varchar(<b>20</b>), dt_date datetime, dt_num int)

declare @cnt int
declare @cnt_offset int
set @cnt_offset = <b>1</b>
set @cnt = <b>1</b>


while (select @cnt) < <b>30000</b>
begin
if (@cnt_offset > <b>20</b>)
set @cnt_offset = <b>1</b>

insert into #test values(replicate(char(@cnt_offset+<b>100</b>), <b>15</b>), dateadd(d, @cnt_offset, getdate()), @cnt_offset ) 
set @cnt = @cnt + <b>1</b>
set @cnt_offset = @cnt_offset + <b>1</b>
end

update #test set dt_num = <b>100</b> where dt_num = <b>10</b>
select * from #test

drop table #test
Время выполнения: 1164 msУвеличение счетчика до 300 000 = 11383 msИтог. Уже при использовании 30 000 записей в таблицы-переменные уступают скорости временным в tempdb. Тенденция растет при 300 000 записей. Утверждение -DocSerzh- корректно. @table переменные более подходят для небольших таблиц. #Временные таблицы показали большее быстродействие в приведенном примере, к тому же при использовании exec('sql') конструкций @table переменные не поддерживаются