Запрос на присвоение порядкового номера строкам без группировки и без изменения порядка строк

Эта таблица состоит из рейсов грузовиков, перевозящих контейнеры в другое место. Каждый раз, когда грузовик перевозит контейнер, записи сохраняются. Если он перевозит 1 единицу, то я сохраняю 1 единицу в поле Unitcount, а также присваиваю TripCount значение 1. Если грузовик перевозит 2 единицы, то значение поля Unitcount сохраняется как 2 единицы, а TripCount — как 0,5.

Ниже приведено содержимое таблицы — здесь Tid — это первичный ключ:

Мне нужен следующий вывод:

То есть, если водитель перевез 2 единицы, то значение Work Serial должно хранить номер. рейсов он отработал и должен сохранять постоянное значение для рейсов, в которых он перевез 2 единицы. Цель этого вывода состоит в том, что я буду использовать этот столбец «Серийный номер работы» для расчета стоимости поездки для его поездок. Например, за первую поездку он получает 20 долларов, за вторую поездку 20 долларов, за третью поездку 25 долларов, а за четвертую поездку он получает 30 долларов. Моя проблема в том, что, как бы я ни пытался, мой запрос рассчитывается как 6 поездок, на самом деле он выполнил только 4 поездки. Надеюсь, я смог объяснить свое требование. Я понимаю, это очень простой запрос; но, к сожалению, я не могу решить.

Обратите внимание, порядок строк менять нельзя.

Извините, что не понятно объяснил. Это была моя ошибка, так как я указал только одно имя водителя и одну дату. На самом деле я рассчитываю стоимость поездки водителя за конкретную неделю и для всех водителей. Хотя я скопировал возможные значения из таблицы и вставил сюда.

введите здесь описание изображения

Используйте приведенные ниже сценарии для создания таблицы и вставки данных (в качестве обходного пути).

CREATE TABLE [dbo].[Test_Table](
[Tid] [bigint] NOT NULL,
[DriverName] [nvarchar](50) NULL,
[Cardgdate] [date] NULL,
[Dircid] [int] NULL,
[Load] [nvarchar](50) NULL,
[UnitCount] [nchar](10) NULL,
[Result] [int] NULL,
CONSTRAINT [PK_Test_Table] PRIMARY KEY CLUSTERED 
(
    [Tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253293, N'Naveed Khan', '20200823 00:00:00.000', 34, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253320, N'Aas Muhammad', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253358, N'Danish Imtiaz', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253407, N'Naveed Khan', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253434, N'Aas Muhammad', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253449, N'Danish Imtiaz', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253492, N'Naveed Khan', '20200823 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253516, N'Danish Imtiaz', '20200824 00:00:00.000', 34, N'Agent', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253520, N'Naveed Khan', '20200824 00:00:00.000', 34, N'Agent', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253525, N'Aas Muhammad', '20200824 00:00:00.000', 34, N'Agent', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253576, N'Danish Imtiaz', '20200824 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253592, N'Aas Muhammad', '20200824 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (253599, N'Naveed Khan', '20200824 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254647, N'Danish Imtiaz', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254659, N'Naveed Khan', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254664, N'Aas Muhammad', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254711, N'Danish Imtiaz', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254743, N'Chandra Shekar', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254744, N'Aas Muhammad', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254745, N'Naveed Khan', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254783, N'Danish Imtiaz', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254785, N'Chandra Shekar', '20200825 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254802, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'2 Units   ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254803, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'2 Units   ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254815, N'Chandra Shekar', '20200826 00:00:00.000', 34, N'Agent', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254833, N'Aas Muhammad', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254900, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254904, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'2 Units   ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254905, N'Naveed Khan', '20200826 00:00:00.000', 39, N'Terminal', N'2 Units   ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254927, N'Chandra Shekar', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254964, N'Aas Muhammad', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254986, N'Danish Imtiaz', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254987, N'Naveed Khan', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)
INSERT INTO [dbo].[Test_Table]([Tid], [DriverName], [Cardgdate], [Dircid], [Load], [UnitCount], [Result]) VALUES (254992, N'Chandra Shekar', '20200826 00:00:00.000', 30, N'Terminal', N'1 Unit    ', 0)

sql sql-server datetime window-functions gaps-and-islands
person Shaaaan    schedule 21.09.2020    source источник
comment
Ваши результаты, кажется, зависят от порядка строк, но нет столбца, который определяет порядок. Кроме того, если tid является первичным ключом, почему значения повторяются?   -  person Gordon Linoff    schedule 21.09.2020
comment
@GordonLinoff, извините, стол на самом деле очень большой. Я копирую и вставляю в Excel, чтобы объяснить контекст. Извините .. Я проглядел при вставке ... извиняюсь. Я сейчас отредактировал.   -  person Shaaaan    schedule 21.09.2020

Ответы (3)


arrow_upward
0
arrow_downward

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

drop table if exists test_table;
create table test_table as
select 254802 as id, 0.5 as TripCount,'Abdul' as DriverName,'2units' as units union
select 254803 as id, 0.5 as TripCount, 'Abdul' as DriverName,'2units' as units  union
select 254987 as id, 1 as TripCount, 'Abdul' as DriverName,'1units' as units  union
select 254910 as id, 0.5 as TripCount,'Abdul' as DriverName,'2units' as units  union
select 254911 as id, 0.5 as TripCount,'Abdul' as DriverName,'2units' as units  union
select 254912 as id, 1 as TripCount,'Abdul' as DriverName,'1units' as units  ;

select id,TripCount,DriverName,unit_flg,
sum (TripCount) over (partition by DriverName,unit_flg order by  id rows unbounded preceding )   cumulative_trips
from (select *,case when units='2units' then 1 else 0 end as unit_flg
from test_table ) a where a.unit_flg>0 group by 1,2,3,4;
person Python noob    schedule 21.09.2020
comment
хорошо, я работаю с MSSQL2012. Я ценю ваше предложение. Я попробовал ваш код; и получил 4 строки со значением Cumaltive_trips как 1,2,3 и 4... На самом деле мое требование состояло в том, чтобы объединить строки значений «2 единиц», которые будут рассматриваться как 1. Моя таблица запросов имеет 6 строк. Запрос должен сократить это количество до 4 строк (считая поездки 2 единиц за 1 поездку). Хотя я очень ценю ваши усилия. и большое спасибо - person Shaaaan; 22.09.2020
comment
@Shaaaan Я знаю, что вы уже получили свой ответ, если бы вы просто изменили сумму на unit_flag, вы бы получили свои 4 поездки. Чтобы удалить 1 единицу поездок, вы можете добавить оператор where where units<>'1units', в противном случае вы можете оставить все - это не изменит совокупное количество поездок. (select b.*, sum (unit_flg) over (partition by DriverName order by id rows unbounded preceding ) as cumulative_trips from(select id,TripCount,DriverName,unit_flg from (select *,case when units='2units' then 1 else 0 end as unit_flg from test_table ) a group by 1,2,3,4) b group by 1,2,3,4; - person Python noob; 24.09.2020

arrow_upward
0
arrow_downward

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

Вы можете присвоить нужное значение, просмотрев строки, в которых некоторые столбцы совпадают — я думаю, это имя водителя, дата карты, dircid, загрузка и количество единиц. Затем вы можете рассматривать это как проблему пробелов и островов. Проверьте, когда эти значения изменяются, и сделайте кумулятивную сумму:

select t.*,
       sum(case when prev_tid_grp = prev_tid then 0  -- no change in the key columns
                else 1                               -- count the change!
           end) over (order by tid) as workserial
from (select t.*,
             lag(tid) over (order by tid) as prev_tid,
             lag(tid) over (partition by rivername, cardgdate, dircid, load, unitcount order by tid) as prev_tid_grp
      from t
     ) t;
person Gordon Linoff    schedule 21.09.2020
comment
Большое спасибо за совет. Действительно, я работал с вашим кодом... и затем я получил результат.. Я ценю вашу мгновенную поддержку.... Большое спасибо - person Shaaaan; 22.09.2020
comment
@ Шаааан . . . Я удивлен, что ты не принял этот ответ тогда. - person Gordon Linoff; 22.09.2020
comment
Что ж, прошли годы, когда я запросил в stackoverflow... Я был в облаках, когда мгновенно получил решение и был занят, продолжая свою работу.... :) - person Shaaaan; 22.09.2020

arrow_upward
0
arrow_downward

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

Если это так, вы можете использовать lag(), тогда окно count(). Неясно, какой столбец можно использовать для упорядочения строк, поэтому я предположил id.

select t.*,
    sum(case when unitcount = lag_unitcount then 0 else 1 end)
        over(partition by drivename order by id) worserial      
from (
    select t.*, 
        lag(unitcount) over(partition by drivename order by id) lag_unitcount
    from mytable t
) t
person GMB    schedule 21.09.2020
comment
Большое вам спасибо .. Вау ... Я не знал, что stackoverflow может творить чудеса ... Боже .... именно это мне и было нужно. Я должен тебе лотззззз. - person Shaaaan; 22.09.2020