Два виртуально вычисляемых столбца в поле зрения

Изначально в обычной таблице есть два столбца Key и ParentKey. Key автоматически получает свое значение как столбец идентификаторов. ParentKey - по выражению

 WITH
  latest
  as
(
  SELECT
   ProductID,
   Date,
   [Key],
    ROW_NUMBER() OVER (
      PARTITION BY  ProductID
          ORDER BY [Date] DESC -- order by latest Date
    ) rn 
  FROM
   [MyTable]
)


UPDATE      
  u 
SET
  u.[ParentKey] = latest.[Key] 
FROM
   [MyTable] u
INNER JOIN
  latest
    ON u.ProductID = latest.ProductID
WHERE
  latest.rn = 1

Можно ли совместить оба этих шага одновременно при создании View (Key и ParentKey становятся виртуальными)? У меня есть решение для первой части этой задачи - создание ключевого столбца в представлении. Можно ли это совместить с шагом, который затем устанавливает ParentKey?

Текущий код

CREATE VIEW v_Test 

AS
SELECT
ProductID
,Date,
CAST (ROW_NUMBER() OVER( ORDER BY [ProductID] ) AS int) as [Key]

-- some expression for ParentKey?

FROM [MyTable]

Желаемый вывод представления (Key и ParentKey должны быть обработаны во время создания представления)

+-------------------------------------------------
|ProductID  |   Date        |  Key   |  ParentKey 
+-------------------------------------------------
|111        |   2017-12-31  |   1    |   3
|111        |   2018-12-31  |   2    |   3
|111        |   2019-12-31  |   3    |   3
|222        |   2017-12-31  |   4    |   6
|222        |   2018-12-31  |   5    |   6
|222        |   2019-12-31  |   6    |   6
|333        |   2017-12-31  |   7    |   9
|333        |   2018-12-31  |   8    |   9
|333        |   2019-12-31  |   9    |   9

Как правило: просто определите выражение, например A + B AS Something.

Bogdan Sahlean 14.02.2023 12:18

Простое использование: SELECT *, MAX(Key) OVER(PARTITION BY ProductID) Key2 FROM (ваш запрос) x

Bogdan Sahlean 14.02.2023 12:46
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
2
68
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

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

CREATE VIEW v_Test
AS
  WITH T
       AS (SELECT ProductID,
                  Date,
                  ROW_NUMBER() OVER(ORDER BY [ProductID] ASC,  [Date] ASC ) AS [Key]
           FROM   [MyTable])
  SELECT ProductID,
         Date,
         [Key],
         MAX([Key]) OVER (PARTITION BY [ProductID]) AS [ParentKey]
  FROM   T 

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

Рекурсивный CTE — это то, что нужно, если ключи могут измениться, но они могут быть дорогостоящими в вычислительном отношении, и существует ограничение на количество рекурсий, по умолчанию оно равно 100, но его можно переопределить с помощью подсказки, если вы не находитесь в таблице. функция, в этом случае она фиксируется на 100. Другим вариантом может быть добавление логики к триггеру. Я не большой поклонник триггеров, их можно отключить и иметь неожиданные последствия при использовании @@IDENTITY, но если отношения никогда не меняются, они могут быть действительны здесь.

Aaron Reese 14.02.2023 12:48

@AaronReese - Если у вас есть ответ, опубликуйте его. Не оставляйте это как неуместный комментарий к моему. Я не согласен, что рекурсивные CTE могут здесь что-то предложить.

Martin Smith 14.02.2023 12:50

Извините, мой плохой, я просмотрел сообщение и предположил, что ОП искал иерархические отношения для цепочки доказательств, а ваше представление использовало рекурсивный CTE, поэтому подтверждало ваше намерение. Вы правы, рекурсивный CTE здесь бесполезен. Не уверен, что вам нужен CTE в вашем запросе. OP говорит, что ключ является автоматически заполняемым полем IDENTITY, поэтому вы можете быть уверены, что MAX (ключ) является последней записью.

Aaron Reese 14.02.2023 12:59

@AaronReese - столбец IDENTITY является начальным состоянием. ("Изначально в обычной таблице") Почему-то все это дело пытаются заменить "виртуальными" столбцами

Martin Smith 14.02.2023 13:02

Если я правильно вас понял, я считаю, что будет намного короче, используя только MAX с предложением OVER. Что-то вроде

UPDATE      
  u 
SET
  u.[ParentKey] = 
FROM
   [MyTable] u
CROSS APPLY (SELECT MAX( [Key] OVER ( PARTITION BY ProductID ORDER BY [DATE] DESC ) LatestKey ) ca 

Другие вопросы по теме