Анрил таск. Преобразование данных

@UseMuse · Регистрация: 11.01.2016

Студворк — интернет-сервис помощи студентам

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
--таблица данных, в которой хранится  информация о пути проделанном из точки А в точку Б, 
-- во время пути были стоянки
--ParkingTime секунды простоя на парковке
--dist пройденный путь от одной записи к другой
DECLARE @DataPath TABLE (ParkingTime bigint, dist int, recordDate datetime)
 
INSERT @DataPath VALUES (37520300000,0,GETDATE())
                            ,(930300000,28,GETDATE())
                            ,(0,42,GETDATE())
                            ,(0,64,GETDATE())
                            ,(200000000,58,GETDATE())
                            ,(0,65,GETDATE())
                            ,(0,84,GETDATE())
                            ,(0,83,GETDATE())
                            ,(0,76,GETDATE())
 
-- нужно из таблицы @DataPath записать данные в новую результирующую таблицу
 
-- Number 
--,  первая запись number = 1 
-- Number увеличивать  каждый раз на 1, если данные ParkingTime > 0 из таблицы  @DataPath
-- получится для первых 5 объектов из таблицы @DataPath результат для поля Number будет : 1, 2, 2, 2, 3
 
-- ParkingTime секунды переконвертировать в time
 
-- all_dist = текущее значение dist + предудущие значение dist
 
-- dist_from_prev_parking расстояние от текущей записи до  предыдущей парковки 
 
-- dist - это dist из таблицы  @DataPath 
-- recordTime записать время  из recordDate
-- recordTimePlusparkingTime записать время  из recordDate + ParkingTime
-- recordDate - это данные из recordDate
DECLARE @DataResult TABLE (Number int , ParkingTime time , all_dist int, dist_from_prev_parking int null, dist int
                                        , recordTime time, recordTimePlusparkingTime time, recordDate datetime)

@Jefe · 15.11.2018, 19:20

Сообщение от UseMuse

-- Number
--, *первая запись number = 1
-- Number увеличивать *каждый раз на 1, если данные ParkingTime > 0 из таблицы *@DataPath
-- получится для первых 5 объектов из таблицы @DataPath результат для поля Number будет : 1, 2, 2, 2, 3

в оракле это можно сделать так:

SQL
1
SUM(CASE WHEN PARKINGTIME <> 0 THEN 1 END) OVER (ORDER BY rownum) AS "Number"

В MSSQL, кажется, нет аналога rownum, поэтому надо будет искуственно проставить нумерацию строк, по которой сортировать

Добавлено через 2 минуты

Сообщение от UseMuse

-- ParkingTime секунды переконвертировать в time

37520300000 секунд это более 1000 лет, может надо отбросить несколько нулей?

Добавлено через 1 минуту

Сообщение от UseMuse

-- all_dist = текущее значение dist + предудущие значение dist

SQL
1
SUM(dist) OVER (ORDER BY <столбец с порядком>)

Добавлено через 4 минуты

Сообщение от UseMuse

-- dist_from_prev_parking расстояние от текущей записи до *предыдущей парковки

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

@pincet · 16.11.2018, 10:12

Сообщение от Jefe

В MSSQL, кажется, нет аналога rownum, поэтому надо будет искуственно проставить нумерацию строк, по которой сортировать

ROW_NUMBER() если версия сервера позволяет

@invm · 16.11.2018, 11:49

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
with t as
(
 select
  sum(case when ParkingTime = 0 then 0 else 1 end) over (order by recordDate) as Number,
  sum(dist) over (order by recordDate) as all_dist,
  * 
 from
  @Datapath
)
select
 Number, ParkingTime, all_dist,
 sum(dist) over (partition by Number order by recordDate) - first_value(dist) over (partition by Number order by recordDate) as dist_from_prev_parking,
 dist, recordDate
from
 t
order by
 recordDate;

С таймами либо разбирайтесь сами, либо внятно обьъясните, что это и как их нужно обработать.

@UseMuse · 19.11.2018, 11:03 **[ТС]**

invm, доброе утро, такой должен быть ожидаемый результат.
Можно ли мой вариант более элегантнее чтоли написать?

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
    DECLARE @Number smallint = null
    DECLARE @Distance decimal(7,2)
    DECLARE @pre_dist decimal(7,2)
    DECLARE @dist_covered decimal(7,2) = 0
    DECLARE @dist_from_prev_parking decimal(7,2) = null
 
    DECLARE @ParkingTime bigint
    DECLARE @recordDate datetime
 
    DECLARE StageCursor CURSOR FOR
 
    SELECT 
           datas.ParkingTime AS ParkingTime,
             -- время записи предыдущей записи
           LAG(datas.dist, 1) OVER(ORDER BY datas.recordDate),
           datas.dist,
           datas.recordDate
         
    FROM @Datapath as datas
    OPEN StageCursor 
    FETCH NEXT FROM StageCursor INTO  @ParkingTime,@pre_dist,@Distance,@recordDate
    WHILE @@FETCH_STATUS = 0
    BEGIN
 
    DECLARE @IsFirst BIT  = 0
    DECLARE @IsParking BIT  = 0
 
        -- Первая запись
        IF @Number IS NULL
        BEGIN
            SET @IsFirst = 1
            SET @Number = 1 
            SET @Distance = 0
            SET @ParkingTime = 0
        END
 
        IF (@ParkingTime>0) 
        BEGIN
            SET @IsParking = 1
            if(@IsFirst = 0)
            SET @Number = @Number + 1
        END
 
        if(@IsFirst = 0)
        SET @dist_from_prev_parking = @dist_from_prev_parking + @Distance 
 
        SET @dist_covered = @dist_covered + @Distance
        DECLARE @ForInsertParkingTime time =  CONVERT(VARCHAR(12),DATEADD(SECOND, @ParkingTime / 10000000, '19700101 00:00'), 114)
 
        INSERT INTO @Result VALUES(@Number, @ForInsertParkingTime,@Distance /1000,
         @dist_from_prev_parking /1000,@dist_covered /1000,@recordDate)
 
        IF (@IsParking = 1) 
            SET @dist_from_prev_parking = 0
 
        -- Следующий элемент курсора ячеек, пока есть записи
        FETCH NEXT FROM StageCursor INTO   @ParkingTime,@pre_dist,@Distance,@recordDate
    END
    CLOSE StageCursor
    DEALLOCATE StageCursor
 
--вывод результата. Ожидаемый результат
    select * from @Result

Добавлено через 1 минуту

Сообщение от Jefe

37520300000 секунд это более 1000 лет, может надо отбросить несколько нулей?

Это в тиках, чтобы конвертировать тики в секунды нужно тики разделить на 10000000

@invm · 19.11.2018, 11:48

UseMuse, ваш скрипт нерабочий т.к. не соответствует примеру и структуре данных из стартового поста.

@UseMuse · 19.11.2018, 12:23 **[ТС]**

invm,

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
--таблица данных, в которой хранится  информация о пути проделанном из точки А в точку Б, 
-- во время пути были стоянки
--ParkingTime секунды простоя на парковке
--dist пройденный путь от одной записи к другой
DECLARE @DataPath TABLE (ParkingTime bigint, dist int, recordDate  datetime)
 
INSERT @DataPath VALUES (37520300000,0,GETDATE())
                            ,(930300000,28,GETDATE())
                            ,(0,42,GETDATE())
                            ,(0,64,GETDATE())
                            ,(200000000,58,GETDATE())
                            ,(0,65,GETDATE())
                            ,(0,84,GETDATE())
                            ,(0,83,GETDATE())
                            ,(0,76,GETDATE())
 
                            select * from @DataPath
-- нужно из таблицы @DataPath записать данные в новую результирующую таблицу
 
-- Number 
--,  первая запись number = 1 
-- Number увеличивать  каждый раз на 1, если данные ParkingTime > 0 из таблицы  @DataPath
-- получится для первых 5 объектов из таблицы @DataPath результат для поля Number будет : 1, 2, 2, 2, 3
 
-- ParkingTime секунды переконвертировать в time
 
-- all_dist = текущее значение dist + предудущие значение dist, 
-- получится для первых 5 объектов из таблицы @DataPath результат для поля all_dist будет : 42,70,112,176,234 
 
-- dist_from_prev_parking расстояние от текущей записи до  предыдущей парковки 
 
-- dist - это dist из таблицы  @DataPath 
-- recordTime записать время  из recordDate 
-- recordTimePlusparkingTime записать время  из recordDate  + ParkingTime
-- recordDate  - это данные из recordDate 
DECLARE @Result TABLE (Number int , ParkingTime time , dist decimal(7,2), dist_from_prev_parking decimal(7,2) null
                                        , dist_covered decimal(7,2), recordDate  datetime);
 
    DECLARE @Number smallint = null
    DECLARE @Distance decimal(7,2)
    DECLARE @pre_dist decimal(7,2)
    DECLARE @dist_covered decimal(7,2) = 0
    DECLARE @dist_from_prev_parking decimal(7,2) = null
 
    DECLARE @ParkingTime bigint
    DECLARE @recordDate  datetime
 
    DECLARE StageCursor CURSOR FOR
 
    SELECT 
           datas.ParkingTime AS ParkingTime,
             -- время записи предыдущей записи
           LAG(datas.dist, 1) OVER(ORDER BY datas.recordDate ),
           datas.dist,
           datas.recordDate 
         
    FROM @DataPath as datas
    OPEN StageCursor 
    FETCH NEXT FROM StageCursor INTO  @ParkingTime,@pre_dist,@Distance,@recordDate 
    WHILE @@FETCH_STATUS = 0
    BEGIN
 
    DECLARE @IsFirst BIT  = 0
    DECLARE @IsParking BIT  = 0
 
        -- Первая запись
        IF @Number IS NULL
        BEGIN
            SET @IsFirst = 1
            SET @Number = 1 
            SET @Distance = 0
            SET @ParkingTime = 0
        END
 
        IF (@ParkingTime>0) 
        BEGIN
            SET @IsParking = 1
            if(@IsFirst = 0)
            SET @Number = @Number + 1
        END
 
        if(@IsFirst = 0)
        SET @dist_from_prev_parking = @dist_from_prev_parking + @Distance 
 
        SET @dist_covered = @dist_covered + @Distance
        DECLARE @ForInsertParkingTime time =  CONVERT(VARCHAR(12),DATEADD(SECOND, @ParkingTime / 10000000, '19700101 00:00'), 114)
 
        INSERT INTO @Result VALUES(@Number, @ForInsertParkingTime,@Distance /1000,
         @dist_from_prev_parking /1000,@dist_covered /1000,@recordDate )
 
        IF (@IsParking = 1) 
            SET @dist_from_prev_parking = 0
 
        -- Следующий элемент курсора ячеек, пока есть записи
        FETCH NEXT FROM StageCursor INTO   @ParkingTime,@pre_dist,@Distance,@recordDate 
    END
    CLOSE StageCursor
    DEALLOCATE StageCursor
 
 
    select * from @Result

@invm · 19.11.2018, 12:40

UseMuse, первое и самое главное - должен быть столбец, определяющий порядок строк.
Без такового задача не имеет решения. Вообще.

@UseMuse · 19.11.2018, 12:52 **[ТС]**

invm, мне мой вариант устраивает, хотел просто посмотреть другую реализацию такого же результата

@invm · 19.11.2018, 13:01

Сообщение от UseMuse

invm, мне мой вариант устраивает

Ок.
Возвращайтесь, когда получите неправильный результат.

Новые блоги и статьи Все статьи Все блоги /
Валидация и контроль данных табличной части документа перед записью Maks 22.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа, разработанного в КА2. Задача: контроль и валидация данных табличной части документа перед записью с учетом регламента компании. . .	Отчёт о затраченных материалах за определенный период с макетом печатной формы Maks 21.04.2026 Отчёт из решения ниже размещён в конфигурации КА2. Задача: разработка отчёта по затраченным материалам за определённый период, с возможностью вывода печатной формы отчёта с шапкой и подвалом. В. . .	Отчёт о спецтехнике находящейся в ремонте Maks 20.04.2026 Отчёт из решения ниже размещен в конфигурации КА2. Задача: отобразить спецтехнику, которая на данный момент находится в ремонте. Есть нетиповой документ "Заявка на ремонт спецтехники" который. . .	Памятка для бота и "визитка" для читателей "Semantic Universe Layer (Слой семантической вселенной)" Hrethgir 19.04.2026 Сгенерировано для краткого описания по случаю сборки и компиляции скелета серверного приложения. И пусть после этого скажут, что статьи сгенерированные AI - туфта и не интересно. И это не реклама -. . .
Запрет удаления строк ТЧ документа при определённом условии Maks 19.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "Аккумуляторы", разработанного в конфигурации КА2. У данного документа есть ТЧ, в которой в зависимости от прав доступа. . .	Модель заражения группы наркоманов alhaos 17.04.2026 Условия задачи сформулированы тут Суть: - Группа наркоманов из 10 человек. - Только один инфицирован ВИЧ. - Колются одной иглой. - Колются раз в день. - Колются последовательно через. . .	Мысли в слух. Про "навсегда". kumehtar 16.04.2026 Подумалось тут, что наверное очень глупо использовать во всяких своих установках понятие "навсегда". Это очень сильное понятие, и я только начинаю понимать край его смысла, не смотря на то что давно. . .	My Business CRM MaGz GoLd 16.04.2026 Всем привет, недавно возникла потребность создать CRM, для личных нужд. Собственно программа предоставляет из себя базу данных клиентов, в которой можно фиксировать звонки, стадии сделки, а также. . .

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	19.11.2018, 11:48
	UseMuse, ваш скрипт нерабочий т.к. не соответствует примеру и структуре данных из стартового поста. 0

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	19.11.2018, 12:40
	UseMuse, первое и самое главное - должен быть столбец, определяющий порядок строк. Без такового задача не имеет решения. Вообще. 0

@UseMuse 154 / 154 / 60 Регистрация: 11.01.2016 Сообщений: 1,325
	19.11.2018, 12:52 [ТС]
	invm, мне мой вариант устраивает, хотел просто посмотреть другую реализацию такого же результата 0