Полезное руководство по преобразованию запросов из формата Microsoft SQL в Teradata SQL.
В приведенных примерах ссылка на объект «mufford» — это некое личное хранилище. Следует заменять на собственное.
Команда SELECT
Простой SELECT
SQL Server
USE AdventureWorksDW2012;
GO
SELECT TOP 10 ProductKey
, EnglishProductName
FROM dbo.DimProduct;
или
SELECT TOP 10 ProductKey
, EnglishProductName
FROM AdventureWorksDW2012.dbo.DimProduct;
Teradata
SELECT TOP 10 P_PartKey
, P_Name
FROM retail.Product;
Команда UPDATE
SQL Server
Чтобы продемонстрировать более сложный UPDATE, сначала будет создана временная таблица, добавлены данные в эту таблицу, а затем они будут модифицированы с помощью UPDATE.
CREATE TABLE #UpdateTest_firstOrder
(
ProductKey INT
, FirstOrderNumber NVARCHAR(20) NULL
);
INSERT INTO #UpdateTest_firstOrder(ProductKey)
SELECT DISTINCT ProductKey
FROM dbo.DimProduct;
UPDATE ufo
SET FirstOrderNumber = COALESCE(x.FirstOrderNumber, '-1')
FROM #UpdateTest_firstOrder ufo
JOIN
(
SELECT ProductKey
, MIN(SalesOrderNumber) AS FirstOrderNumber
FROM dbo.FactInternetSales
GROUP BY ProductKey
) AS x
ON ufo.ProductKey = x.ProductKey;
Teradata
Аналогичные манипуляции будут произведены в Teradata. Однако для этого есть два варианта, продемонстрированные ниже.
CREATE VOLATILE TABLE UpdateTest_firstOrder
(
L_PartKey INTEGER
, FirstOrderKey INTEGER NULL
)
PRIMARY INDEX
(
L_PartKey
)
ON COMMIT PRESERVE ROWS;
INSERT INTO UpdateTest_firstOrder(L_PartKey)
SELECT DISTINCT P_PartKey
FROM retail.Product;
-- Option 1
UPDATE UpdateTest_firstOrder
FROM (
SELECT L_PartKey
, MIN(L_OrderKey) AS FirstOrderKey
FROM retail.Item
GROUP BY L_PartKey
) AS x
SET FirstOrderKey = COALESCE(x.FirstOrderKey, -1)
WHERE UpdateTest_firstOrder.L_PartKey = x.L_PartKey;
-- Option 2
UPDATE UpdateTest_firstOrder
SET FirstOrderKey = FirstOrderKey
WHERE EXISTS(
SELECT L_PartKey
, MIN(L_OrderKey) AS firstOrderID
FROM retail.Item
WHERE UpdateTest_firstOrder.L_PartKey = retail.Item.L_PartKey
GROUP BY L_PartKey
);
Команда DELETE
Delete Top X
SQL Server
Это довольно просто сделать в SQL Server.
DELETE TOP (1)
FROM dbo.DimEmployee
WHERE BirthDate < '2012-01-01';
Teradata
В Teradata это немного сложнее. Нужно сначала создать временную таблицу, содержащую строки, которые нужно удалить. Затем выполнить DELETE используя оператор IN.
CREATE TABLE mufford.ToBeDeleted AS
(
SELECT TOP 1 EmpNo
FROM retail.Employee
WHERE DOB < DATE'2012-01-01'
)
WITH DATA;
DELETE FROM retail.Employee
WHERE EmpNo IN (SELECT EmpNo FROM mufford.ToBeDeleted);
Удаление с условием
SQL Server
DELETE fis
FROM dbo.FactInternetSales AS fis
JOIN dbo.DimSalesTerritory AS dst
ON fis.SalesTerritoryKey = dst.SalesTerritoryKey
WHERE dst.SalesTerritoryCountry <> 'United States';
Teradata
Снова можно сделать удаление, воспользовавшись подзапросом.
DELETE FROM retail.Item
WHERE L_PartKey IN
(
SELECT P_PartKey
FROM retail.Product
WHERE P_Mfgr = 'Manufacturer#1'
);
Удаление, когда условие не выполняется
SQL Server
DELETE fis
FROM dbo.DimProduct AS dp
LEFT JOIN dbo.FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey
WHERE fis.SalesOrderNumber IS NULL;
Teradata
Опять используется подзапрос.
DELETE FROM retail.Item
WHERE NOT EXISTS(
SELECT P_PartKey
FROM retail.Product
WHERE retail.Item.L_PartKey = retail.Product.P_PartKey
);
Временные таблицы
Создание простой временной таблицы
SQL Server
Простой путь:
SELECT LastName, FirstName
INTO #MyTempTable
FROM dbo.DimEmployee
WHERE DepartmentName = 'Engineering';
Многословный "более практичный" вариант:
CREATE TABLE #MyTempTable
(
LastName NVARCHAR(50)
, FirstName NVARCHAR(50)
);
INSERT INTO #MyTempTable
(
LastName
, FirstName
)
SELECT LastName
, FirstName
FROM dbo.DimEmployee
WHERE DepartmentName = 'Engineering';
Teradata
В Teradata эквивалентом временных таблиц являются Volatile Tables.
CREATE VOLATILE TABLE MyTempTable
AS (
SELECT Name
FROM retail.Employee
WHERE DeptNo = 1420
)
WITH DATA ON COMMIT PRESERVE ROWS;
Работа с датами
Найти за последние X дней
SQL Server
SELECT SalesOrderNumber
FROM dbo.FactInternetSales
WHERE OrderDate >= DATEADD(day, -30, GETDATE());
Teradata
SELECT L_OrderKey
FROM retail.Item
WHERE l_shipdate >= CURRENT_DATE - INTERVAL '30' DAY;
Подсчет дней между двумя датами
SQL Server
SELECT TOP 10 OrderDate
, ShipDate
, DATEDIFF(day, OrderDate, ShipDate) AS 'daysLapsed'
FROM dbo.FactInternetSales;
-- OR
SELECT TOP 10 OrderDate
, ShipDate
, CAST(ShipDate - OrderDate AS INT) AS 'daysLapsed'
FROM dbo.FactInternetSales;
Teradata
SELECT L_ShipDate
, L_ReceiptDate
, L_ReceiptDate - L_ShipDate AS "daysLapsed"
FROM retail.Item;
Использование дат в переменных
SQL Server
CREATE PROCEDURE spReturnOrdersBetweenDaysExample
(
@StartDate DATETIME
, @EndDate DATETIME
)
AS
SELECT *
FROM dbo.FactInternetSales
WHERE OrderDate >= @StartDate
AND OrderDate < @EndDate;
EXECUTE spReturnOrdersBetweenDaysExample '2005-07-01', '2005-08-01';
Teradata
Вместо хранимой процедуры будем использовать макрос.
CREATE MACRO mufford.mReturnOrdersBetweenDays
(
startDate DATE
, endDate DATE
)
AS
(
SELECT *
FROM retail.Item
WHERE L_ShipDate >= :startDate
AND L_ShipDate < :endDate;
);
EXECUTE mufford.mReturnOrdersBetweenDays('1993-09-01','1993-10-01');
Если появляется ошибка "An owner referenced by user does not have SELECT WITH GRANT OPTION" при попытке выполнить запрос выше,следует выполнить команду:
GRANT SELECT ON retail.Item TO mufford WITH GRANT OPTION;
Расчёт даты с использованием переменных
SQL Server
CREATE PROCEDURE spReturnOrdersInXDaysExample
(
@StartDate DATETIME
, @numberOfDays INT
)
AS
SELECT *
FROM dbo.FactInternetSales
WHERE OrderDate >= @StartDate
AND OrderDate < DATEADD(day, @numberOfDays, @StartDate);
EXECUTE spReturnOrdersInXDaysExample '2005-07-01', 30;
Teradata
В Teradata для получения того же типа даты будет использоваться макрос вместо хранимой процедуры.
CREATE MACRO mufford.mReturnOrdersInXDaysExample
(
startDate DATE DEFAULT CURRENT_DATE
, numberOfDays INTEGER DEFAULT 30
)
AS
(
SELECT *
FROM retail.Item
WHERE L_ShipDate >= :startDate
AND L_ShipDate < :startDate + CAST(:numberOfDays AS INTERVAL DAY);
);
EXECUTE mufford.mReturnOrdersInXDaysExample('1993-09-01', 30);
Разное
Обработка NULL
SQL Server
SELECT COALESCE(StandardCost, 0) AS [StandardCost]
FROM dbo.DimProduct;
или
SELECT ISNULL(StandardCost, 0) AS [StandardCost]
FROM dbo.DimProduct;
Teradata
SELECT COALESCE(P_RetailPrice, 0) AS "RetailPrice"
FROM retail.Product;