Microsoft SQL Server: генерация порядкового номера в день

sql sql-server tsql sequence

9654 просмотра

5 ответа

Мне поручено создавать увеличивающийся порядковый номер в день для проекта. Несколько процессов (теоретически на нескольких машинах) должны генерировать это. Это заканчивается как

[date]_[number]

любить

20101215_00000001
20101215_00000002
...
20101216_00000001
20101216_00000002
...

Так как я в любом случае использую SQL Server (2008) в этом проекте, я попытался сделать это с помощью магии T-SQL / SQL. Вот где я сейчас нахожусь:

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

CREATE TABLE [dbo].[SequenceTable](
    [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
    [SequenceDate] [date] NOT NULL,
    [SequenceNumber] [int] NULL
) ON [PRIMARY]

Мое наивное решение до сих пор - триггер после вставки, который устанавливает SequenceNumber:

CREATE TRIGGER [dbo].[GenerateMessageId]
ON  [dbo].[SequenceTable] 
AFTER INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- The ID of the record we just inserted
DECLARE @InsertedId bigint;
SET @InsertedId = (SELECT SequenceId FROM Inserted)

-- The next SequenceNumber that we're adding to the new record
DECLARE @SequenceNumber int;
SET @SequenceNumber = (
    SELECT SequenceNumber FROM
    (
        SELECT SequenceId, ROW_NUMBER() OVER(PARTITION BY SequenceDate ORDER BY SequenceDate ASC) AS SequenceNumber
        FROM SequenceTable
    ) tmp
    WHERE SequenceId = @InsertedId
)

-- Update the record and set the SequenceNumber
UPDATE 
    SequenceTable
SET 
    SequenceTable.SequenceNumber = ''+@SequenceNumber
FROM
    SequenceTable
INNER JOIN
    inserted ON SequenceTable.SequenceId = inserted.SequenceId
END

Как я уже сказал, это довольно наивно и содержит целый день строк только для одного числа, которое мне больше никогда не понадобится: я делаю вставку, получаю сгенерированный порядковый номер и впоследствии игнорирую таблицу. Нет необходимости хранить их на моей стороне, мне просто нужно сгенерировать их один раз. Кроме того, я почти уверен, что это не будет хорошо масштабироваться, постепенно становясь медленнее, чем больше строк в таблице (то есть я не хочу попадать в ловушку «работал на моей машине с 10.000 строками»).

Я полагаю, что в настоящее время я более креативно смотрю на SQL, но результат, по-видимому, менее полезен. Более умные идеи?

Автор: Benjamin Podszun Источник Размещён: 12.11.2019 09:32

Ответы (5)


3 плюса

Решение

Забудь об этом SequenceTable. Вам просто нужно создать две колонки в вашей финальной таблице: дату и время. И если вам действительно нужно их объединить, просто добавьте вычисляемый столбец.

Я думаю, это было бы что-то вроде этого:

CREATE TABLE [dbo].[SomeTable] (
    [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
    [SequenceDate] [date] NOT NULL,
    [SequenceNumber] AS (CAST(SequenceDate AS VARCHAR(10)) + '_' + RIGHT('0000000000' + CAST(SequenceID AS VARCHAR(10)), 10)) PERSISTED
) ON [PRIMARY]

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

Редактировать Я все еще думаю, что ответ выше - лучшее решение. НО есть еще один вариант: вычисляемые столбцы могут ссылаться на функции ...

Так что сделайте это:

CREATE FUNCTION dbo.GetNextSequence (
    @sequenceDate DATE,
    @sequenceId BIGINT
) RETURNS VARCHAR(17)
AS
BEGIN
    DECLARE @date VARCHAR(8)
    SET @date = CONVERT(VARCHAR, @sequenceDate, 112)

    DECLARE @number BIGINT
    SELECT
        @number = COALESCE(MAX(aux.SequenceId) - MIN(aux.SequenceId) + 2, 1)
    FROM
        SomeTable aux
    WHERE
        aux.SequenceDate = @sequenceDate
        AND aux.SequenceId < @sequenceId

    DECLARE @result VARCHAR(17)
    SET @result = @date + '_' + RIGHT('00000000' + CAST(@number AS VARCHAR(8)), 8)
    RETURN @result
END
GO

CREATE TABLE [dbo].[SomeTable] (
    [SequenceId] [bigint] IDENTITY(1,1) NOT NULL,
    [SequenceDate] [date] NOT NULL,
    [SequenceNumber] AS (dbo.GetNextSequence(SequenceDate, SequenceId))
) ON [PRIMARY]
GO

INSERT INTO SomeTable(SequenceDate) values ('2010-12-14')
INSERT INTO SomeTable(SequenceDate) values ('2010-12-15')
INSERT INTO SomeTable(SequenceDate) values ('2010-12-15')
INSERT INTO SomeTable(SequenceDate) values ('2010-12-15')
GO

SELECT * FROM SomeTable
GO

SequenceId           SequenceDate SequenceNumber
-------------------- ------------ -----------------
1                    2010-12-14   20101214_00000001
2                    2010-12-15   20101215_00000001
3                    2010-12-15   20101215_00000002
4                    2010-12-15   20101215_00000003

(4 row(s) affected)

Это некрасиво, но работает, верно? :-) Никакой временной таблицы, никаких представлений, триггеров, и она будет иметь приличную производительность (по крайней мере с индексом SequenceIdи SequenceDate, конечно). И вы можете удалить записи (так как и идентичность используется для результирующего вычисляемого поля).

Автор: rsenna Размещён: 15.12.2010 12:42

2 плюса

Если вы можете создать фактическую таблицу с другим именем и выполнить все остальные операции с помощью представления, то это может соответствовать всем требованиям. Также требуется, чтобы ни одна транзакция никогда не удалялась (поэтому вам нужно добавить соответствующий триггер / разрешение для представления / таблицы, чтобы предотвратить это):

create table dbo.TFake (
    T1ID int IDENTITY(1,1) not null,
    T1Date datetime not null,
    Val1 varchar(20) not null,
    constraint PK_T1ID PRIMARY KEY (T1ID)
)
go
create view dbo.T
with schemabinding
as
    select
        T1Date,
        CONVERT(char(8),T1Date,112) + '_' + RIGHT('00000000' + CONVERT(varchar(8),ROW_NUMBER() OVER (PARTITION BY CONVERT(char(8),T1Date,112) ORDER BY T1ID)),8) as T_ID,
        Val1
    from
        dbo.TFake
go
insert into T(T1Date,Val1)
select '20101201','ABC' union all
select '20101201','DEF' union all
select '20101202','GHI'
go
select * from T

Результат:

T1Date  T_ID    Val1
2010-12-01 00:00:00.000 20101201_00000001   ABC
2010-12-01 00:00:00.000 20101201_00000002   DEF
2010-12-02 00:00:00.000 20101202_00000001   GHI

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

Автор: Damien_The_Unbeliever Размещён: 15.12.2010 01:31

1 плюс

Вы могли бы сделать что-то вроде

CREATE TABLE SequenceTableStorage (
    SequenceId bigint identity not null,
    SequenceDate date NOT NULL,
    OtherCol int NOT NULL,
)

CREATE VIEW SequenceTable AS
SELECT x.SequenceDate, (CAST(SequenceDate AS VARCHAR(10)) + '_' + RIGHT('0000000000' + CAST(SequenceID - (SELECT min(SequenceId) + 1 FROM SequenceTableStorage y WHERE y.SequenceDate = x.SequenceDate) AS VARCHAR(10)), 10)) AS SequenceNumber, OtherCol
  FROM SequenceTableStorage x

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

Редактировать:

Приведенный выше код может пропустить некоторые порядковые номера, например, если транзакция вставляет строку, а затем откатывается (значение идентификатора будет потеряно в пространстве).

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

CREATE VIEW SequenceTable AS
SELECT SequenceDate, (CAST(SequenceDate AS VARCHAR(10)) + '_' + RIGHT('0000000000' + row_number() OVER(PARTITION BY SequenceDate ORDER BY SequenceId)
  FROM SequenceTableStorage

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

Автор: erikkallen Размещён: 15.12.2010 01:18

0 плюса

Я попытался таким образом создать сеансовые коды для регистрации пользователей и ее работы;

CREATE FUNCTION [dbo].[GetSessionSeqCode]()
RETURNS VARCHAR(15) 
AS
BEGIN
DECLARE @Count INT;
DECLARE @SeqNo VARCHAR(15)

SELECT @Count = ISNULL(COUNT(SessionCode),0)
FROM UserSessionLog
WHERE SUBSTRING(SessionCode,0,9) =  CONVERT(VARCHAR(8), GETDATE(), 112)

SET @SeqNo =  CONVERT(VARCHAR(8), GETDATE(), 112) +'-' + FORMAT(@Count+1,'D3');

RETURN @SeqNo
END

сгенерированные коды: «20170822-001», «20170822-002», «20170822-003»

Автор: aadhikari Размещён: 22.08.2017 10:32

-1 плюса

Если вы не возражаете против чисел, начинающихся не с одного, вы можете использовать DATEDIFF(dd, 0, GETDATE())количество дней с 1 по 1900. Это будет увеличиваться каждый день.

Автор: Jeff Hornby Размещён: 15.12.2010 12:33
Вопросы из категории :
32x32