Блог

Во-первых, у меня есть эта информация:

1. Вес А
2. Вес B
3. Отношение B к A: 1 ко многим

Таким образом, ниже может быть получен результат:

Теперь добавим еще два столбца: Sum Weight A By B_Id , Accumulative Difference (рассмотрим ниже в виде таблицы t2 )

Например, выше,

1. накопительная разница в первом ряду => 21 — 16 = 5
2. накопительная разница в пятом ряду => (21 8) — (16 12) = 1

Итак, моя цель состоит в том, чтобы вычислить такое « Accumulative Difference Весь результат должен быть отображен в отчете.

Технически, используя «Оконные функции», этого можно достичь без проблем. Во-первых, я должен создать еще 2 столбца: Accumulate Weight A By B_Id , Accumulate Weight B . Затем просто найдите разницу между ними.

На самом деле мне нужно еще 3 колонки:

• [Строка По B_Id]
• [Суммарный Вес A По B_Id]
• [Накопить Вес B]

sample SQL (to generate t2 ):

SELECT
*,
[Accumulate Weight A By B_Id] = SUM(WeightA) OVER (PARTITION BY … ORDER BY B_Id),
[Accumulate Weight B] = SUM(WeightB) OVER (PARTITION BY … ORDER BY B_Id)
FROM t2
— (…) could be by date year month
— Accumulate Weight B can set to only 1st row, etc

 ;WITH tableA AS (
SELECT [A_Id] = 1, [Weight] = 3, [B_Id] = 1, [date] = '2021-10-01'
UNION
SELECT [A_Id] = 2, [Weight] = 5, [B_Id] = 1, [date] = '2021-10-02'
UNION
SELECT [A_Id] = 3, [Weight] = 6, [B_Id] = 1, [date] = '2021-10-03'
UNION
SELECT [A_Id] = 4, [Weight] = 7, [B_Id] = 1, [date] = '2021-10-04'
UNION
SELECT [A_Id] = 5, [Weight] = 2, [B_Id] = 2, [date] = '2021-10-05'
UNION
SELECT [A_Id] = 6, [Weight] = 6, [B_Id] = 2, [date] = '2021-10-06'
    
--Uncomment for testing NULL value
--UNION
--SELECT [A_Id] = 7, [Weight] = 9, [B_Id] = NULL, [date] = '2021-10-07'
--UNION
--SELECT [A_Id] = 8, [Weight] = 10, [B_Id] = 3, [date] = '2021-10-08'
    
),
tableB AS (
     SELECT [B_Id] = 1, [Weight] = 16, [date] = '2021-10-03'
     UNION
     SELECT [B_Id] = 2, [Weight] = 12, [date] = '2021-10-06'

    --Uncomment for testing NULL value
    --UNION
    --SELECT [B_Id] = 3, [Weight] = 8, [date] = '2021-10-08'
),
t1a AS (
    SELECT 
        [A_Id] = tableA.A_Id,
        [WeightA] = tableA.Weight,
        [WeightB] = tableB.Weight,
        [B_Id] = tableB.B_Id,
        [Row By B_Id] = ROW_NUMBER() OVER(PARTITION BY tableB.B_Id ORDER BY A_Id)
    FROM 
        tableA 
    FULL JOIN tableB ON tableA.B_Id = tableB.B_Id
),
t1b AS (
    SELECT
        *,
        [Sum Weight A By B_Id] = SUM(WeightA) OVER (ORDER BY B_Id),
        [Accumulate Weight B] = SUM(CASE WHEN [Row By B_Id] = 1 THEN WeightB ELSE 0 END) OVER (ORDER BY B_Id)
    FROM t1a
),
t2 AS (
    SELECT 
        *,
        [Accumulate Difference] = [Sum Weight A By B_Id] - [Accumulate Weight B]
    FROM t1b
)
SELECT 
    *
FROM t2
 

Теперь возникает проблема, ЕСЛИ один из B_Id них есть NULL . (Раскомментируйте детали, чтобы сгенерировать нулевой B_Id)

Ниже приведен мой ожидаемый результат, особенно в выделенной строке:

Однако с моим примерным запросом это не работает. Вместо этого ниже появится:

Вместо этого в первой строке отображается пустой B_Id. (Заказ испорчен)

Итак, мой вопрос, как справиться с такой ситуацией? (Сохраните исходную строку, как в ожидаемом результате)

Как, например, почему порядок таков? (поднято @ThorstenKettner)

Порядок по умолчанию, основан B_TransactionDatetime . Если B_Id равно NULL, то он будет основан на A_TransactionDatetime . Итак, я вычисляю другой столбец RefDateTime = COALESCE(B_TransactionDatetime, A_TransactionDatetime) и упорядочиваю его на основе этого.

пс:

Вдохновленный @ThorstenKettner, я должен использовать функцию RefDateTime в окне, т. е.:

 [Sum Weight A By B_Id] = SUM(WeightA) OVER (ORDER BY RefDateTime),
[Accumulate Weight B] = SUM(CASE WHEN [Row By B_Id] = 1 THEN WeightB ELSE 0 END) OVER (ORDER BY RefDateTime)
 

Дело закрыто.

Вы хотите внешнее соединение B с A, потому что не у каждого A есть связанный B.

Затем вы смотрите на строки по блокам. Один блок-это либо все строки.принадлежащие одному B, либо одна строка A, в которой нет B. b_id будет хорошим ключом группы для первого, в то время как a_id подойдет для второго. Для комбинированного ключа существуют различные варианты. COALESCE(b_id, a_id) не является одним из них, потому что мы могли бы иметь a_id 1 и b_id 1 в результирующем наборе, но не хотим, чтобы они находились в одной группе. Одно из решений простое COALESCE(b_id, -a_id) , при условии, что ваши идентификаторы, конечно, не могут быть отрицательными.

Теперь все ваши вычисления основаны на агрегированных группах, т. Е. вас не интересуют отдельные значения A, когда они принадлежат группе B. По этой причине я бы сразу же объединил и снова присоединился только к одной строке » А » в самом конце.

Порядок строк таков COALESCE(b_date, a_date) .

  with grouped as
    (
      select
        coalesce(b.b_id, -a.a_id) as grp_id,
        max(coalesce(b.date, a.date)) as grp_date,
        coalesce(max(b.weight), 0) as b_weight,
        sum(a.weight) as a_weight
      from a
      left join b on b.b_id =a.b_id
      group by coalesce(b.b_id, -a.a_id)
    )
    , calculated as
    (
      select
        grp_id,
        grp_date,
        b_weight,
        a_weight,
        sum(a_weight - b_weight) over (order by grp_date) as running_diff
        from grouped
    )
    select *
    from calculated c
    join a on a.b_id = c.grp_id or a.a_id = -c.grp_id
    order by c.grp_date, a.date;
 

Надеюсь, я все понял правильно. У меня нет компьютера под рукой, и я печатаю это на своем мобильном телефоне, что оказывается сложнее, чем я думал 🙂

Вы можете использовать coalesce().

 SELECT 
 *,
 [Accumulate Weight A By B_Id] = SUM(WeightA) OVER (PARTITION BY B_id ORDER BY B_Id),
 [Accumulate Weight B] = SUM(WeightB) OVER (PARTITION BY B_id ORDER BY B_Id),
 SUM(coalesce(WeightA,0)-coalesce(WeightB,0)) OVER (PARTITION BY B_id ORDER BY B_Id) difference

FROM t2
 

PS: На самом деле ваш первоначальный запрос кажется мне неправильным, если бы это было правильно, то это подошло бы.
Вероятно, вам следует привести примеры данных A и B. Для меня имеет больше смысла суммировать (), прежде чем присоединяться к ним вообще.

Вам придется внести изменения, но это должно помочь.`

 SELECT [Accumulate Weight A By B_Id] = SUM(WeightA) OVER (
        PARTITION BY...ORDER BY B_Id
        )
    ,[Accumulate Weight B] = SUM(WeightB) OVER (
        PARTITION BY...ORDER BY B_Id
        )
FROM t2
WHERE B_Id IS NOT NULL

UNION

SELECT [Accumulate Weight A By B_Id] = SUM(TAB.WeightA) OVER (
        PARTITION BY TAB.ROW_NUM ORDER BY B_Id
        )
    ,[Accumulate Weight B] = SUM(TAB.WeightB) OVER (
        PARTITION BY TAB.ROW_NUM ORDER BY B_Id
        )
FROM (
    SELECT WeightA
        ,WeightB
        ,B_Id
        ,ROW_NUMBER() OVER (
            ORDER BY B_ID
            ) AS ROW_NUM
    FROM T2
    WHERE B_ID IS NULL
    ) TAB
 

`