SQL: Как заполнить пустые ячейки предыдущим значением строки?
Я должен произвести столбец, "требуемый" в следующей таблице с помощью SQL, не используя циклы и связанные подзапросы. Действительно ли это возможно в SQL 2008?
Date Customer Value Required Rule
20100101 1 12 12
20100101 2 0 If no value assign 0
20100101 3 32 32
20100101 4 42 42
20100101 5 15 15
20100102 1 12 Take last known value
20100102 2 0 Take last known value
20100102 3 39 39
20100102 4 42 Take last known value
20100102 5 16 16
20100103 1 13 13
20100103 2 24 24
20100103 3 39 Take last known value
20100103 4 42 Take last known value
20100103 5 21 21
20100104 1 14 14
20100104 2 24 Take last known value
20100104 3 39 Take last known value
20100104 4 65 65
20100104 5 23 23
В основном я заполняюсь, пустые ячейки "Значения" с последним знают значение для того клиента. Помните, что последняя строка не может иметь допустимого значения, таким образом, необходимо будет выбрать ее от строки перед этим с допустимым значением.
4 ответа
Фаиз,
как насчет следующего запроса, насколько я понимаю, он делает то, что вы хотите. Комментарии объясняют каждый шаг. Взгляните на CTE в электронной документации. Этот пример можно даже изменить, чтобы использовать новую команду MERGE для SQL 2008.
/* Test Data & Table */
DECLARE @Customers TABLE
(Dates datetime,
Customer integer,
Value integer)
INSERT INTO @Customers
VALUES ('20100101', 1, 12),
('20100101', 2, NULL),
('20100101', 3, 32),
('20100101', 4, 42),
('20100101', 5, 15),
('20100102', 1, NULL),
('20100102', 2, NULL),
('20100102', 3, 39),
('20100102', 4, NULL),
('20100102', 5, 16),
('20100103', 1, 13),
('20100103', 2, 24),
('20100103', 3, NULL),
('20100103', 4, NULL),
('20100103', 5, 21),
('20100104', 1, 14),
('20100104', 2, NULL),
('20100104', 3, NULL),
('20100104', 4, 65),
('20100104', 5, 23) ;
/* CustCTE - This gives us a RowNum to allow us to build the recursive CTE CleanCust */
WITH CustCTE
AS (SELECT Customer,
Value,
Dates,
ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Dates) RowNum
FROM @Customers),
/* CleanCust - A recursive CTE. This runs down the list of values for each customer, checking the Value column, if it is null it gets the previous non NULL value.*/
CleanCust
AS (SELECT Customer,
ISNULL(Value, 0) Value, /* Ensure we start with no NULL values for each customer */
Dates,
RowNum
FROM CustCte cur
WHERE RowNum = 1
UNION ALL
SELECT Curr.Customer,
ISNULL(Curr.Value, prev.Value) Value,
Curr.Dates,
Curr.RowNum
FROM CustCte curr
INNER JOIN CleanCust prev ON curr.Customer = prev.Customer
AND curr.RowNum = prev.RowNum + 1)
/* Update the base table using the result set from the recursive CTE */
UPDATE trg
SET Value = src.Value
FROM @Customers trg
INNER JOIN CleanCust src ON trg.Customer = src.Customer
AND trg.Dates = src.Dates
/* Display the results */
SELECT * FROM @Customers
Мне нужно создать столбец "требуется" в следующей таблице используя SQL без использования циклов и коррелированные подзапросы. Это возможно в SQL 2008?
Невозможно. Точка. Невозможно на ЛЮБОМ сервере на основе SQL, включая oracle.
Основная проблема здесь в том, что вы исключаете циклы и коррелированные подзапросы, и любой способ получения значения во время запроса в конечном итоге будет использовать другой запрос для поиска действительного значения (фактически по одному на поле). Так работает SQL. Да, вы можете скрыть их в специальной скалярной функции, но все же они будут содержать логический подзапрос.
Я не уверен, что следующие пункты считаются, учитывая ваши ограничения, но это поможет в работе.
Тестовые данные
DECLARE @Customers TABLE (Date DATETIME, Customer INTEGER, Value INTEGER)
INSERT INTO @Customers VALUES ('20100101', 1, 12 )
INSERT INTO @Customers VALUES ('20100101', 2, NULL)
INSERT INTO @Customers VALUES ('20100101', 3, 32 )
INSERT INTO @Customers VALUES ('20100101', 4, 42 )
INSERT INTO @Customers VALUES ('20100101', 5, 15 )
INSERT INTO @Customers VALUES ('20100102', 1, NULL)
INSERT INTO @Customers VALUES ('20100102', 2, NULL)
INSERT INTO @Customers VALUES ('20100102', 3, 39 )
INSERT INTO @Customers VALUES ('20100102', 4, NULL)
INSERT INTO @Customers VALUES ('20100102', 5, 16 )
INSERT INTO @Customers VALUES ('20100103', 1, 13 )
INSERT INTO @Customers VALUES ('20100103', 2, 24 )
INSERT INTO @Customers VALUES ('20100103', 3, NULL)
INSERT INTO @Customers VALUES ('20100103', 4, NULL)
INSERT INTO @Customers VALUES ('20100103', 5, 21 )
INSERT INTO @Customers VALUES ('20100104', 1, 14 )
INSERT INTO @Customers VALUES ('20100104', 2, NULL)
INSERT INTO @Customers VALUES ('20100104', 3, NULL)
INSERT INTO @Customers VALUES ('20100104', 4, 65 )
INSERT INTO @Customers VALUES ('20100104', 5, 23 )
Запрос
SELECT c.Date
, c.Customer
, Value = COALESCE(c.Value, cprevious.Value, 0)
FROM @Customers c
INNER JOIN (
SELECT c.Date
, c.Customer
, MaxDate = MAX(cdates.Date)
FROM @Customers c
LEFT OUTER JOIN (
SELECT Date
, Customer
FROM @Customers
) cdates ON cdates.Date < c.Date AND cdates.Customer = c.Customer
GROUP BY
c.Date, c.Customer
) cmax ON cmax.Date = c.Date AND cmax.Customer = c.Customer
LEFT OUTER JOIN @Customers cprevious ON cprevious.Date = cmax.MaxDate AND cprevious.Customer = cmax.Customer
ORDER BY
1, 2, 3
Оператор обновления
UPDATE @Customers
SET Value = c2.Value
OUTPUT Inserted.*
FROM @Customers c
INNER JOIN (
SELECT c.Date
, c.Customer
, Value = COALESCE(c.Value, cprevious.Value, 0)
FROM @Customers c
INNER JOIN (
SELECT c.Date
, c.Customer
, MaxDate = MAX(cdates.Date)
FROM @Customers c
LEFT OUTER JOIN (
SELECT Date
, Customer
FROM @Customers
) cdates ON cdates.Date < c.Date AND cdates.Customer = c.Customer
GROUP BY
c.Date, c.Customer
) cmax ON cmax.Date = c.Date AND cmax.Customer = c.Customer
LEFT OUTER JOIN @Customers cprevious ON cprevious.Date = cmax.MaxDate AND cprevious.Customer = cmax.Customer
) c2 ON c2.Date = c.Date
AND c2.Customer = c.Customer
Как насчет левого внешнего соединения в той же таблице, где дата меньше текущей, а значение не пустое, упорядоченное по дате desc (ограничение 1), возвращение ноль, когда ноль? (На данный момент сервер недоступен для тестирования). Если это не считается подзапросом ...