在 SQL Server 数据库中,可以像这样使用表变量:
In a SQL Server database, one can use table variables like this:
declare @table as table (a int)在 Azure 数据仓库中,这会引发错误.
In an Azure Data Warehouse, that throws an error.
第 1 行解析错误,第 19 列:'table' 附近的语法不正确
Parse error at line: 1, column: 19: Incorrect syntax near 'table'
在 Azure 数据仓库中,您可以使用临时表:
In an Azure Data Warehouse, you can use temporary tables:
create table #table (a int)但不是在函数内部.
消息 2772,级别 16,状态 1,第 6 行无法访问临时表从函数内部.
Msg 2772, Level 16, State 1, Line 6 Cannot access temporary tables from within a function.
这份来自微软的文档说,
◦必须分两步声明(而不是内联):◾CREATE TYPEmy_type AS TABLE ...;, 然后 ◾DECLARE @mytablevariable my_type;.
◦Must be declared in two steps (rather than inline): ◾CREATE TYPE my_type AS TABLE ...; , then ◾DECLARE @mytablevariable my_type;.
但是当我尝试这个时:
create type t as table (a int); drop type t;我明白了:
Msg 103010,级别 16,状态 1,第 1 行解析错误:第 1 行,列:8:'type' 附近的语法不正确.
Msg 103010, Level 16, State 1, Line 1 Parse error at line: 1, column: 8: Incorrect syntax near 'type'.
我的目标是在 Azure 数据仓库中创建一个使用临时表的函数.可以实现吗?
My objective is to have a function in an Azure Data Warehouse which uses a temporary table. Is it achievable?
编辑从这里开始
请注意,我不是在寻找其他方法来创建一个特定的函数.我实际上已经做到了并继续前进.我是一名资深程序员,但也是 Azure 数据仓库新手.我想知道是否可以在 Azure 数据仓库功能中加入一些临时表的概念.
Note that I am not looking for other ways to create one specific function. I have actually done that and moved on. I'm a veteran programmer but an Azure Data Warehouse rookie. I want to know if it's possible to incorporate some concept of temporary tables in an Azure Data Warehouse function.
推荐答案好的,我相信这就是您所追求的.
Ok, I believe this is what you are after.
首先,它使用表值函数,它比标量或多语句表值函数快得多.
Firstly, this uses a Table Value Function, which are significantly faster than Scalar or Multi-statement Table value Functions.
其次,表变量或临时表没有用处,只有一些很好的奇怪字符串操作、一些数学和 CTE.绝对没有昂贵的 WHILE 循环.
Secondly, there was no use for a Table Variable, or Temporary Table, just some good odd string manipulation, a bit of maths, and a CTE. Definitely no expensive WHILE loop.
我已经针对链接中的示例对此进行了测试,它们都返回了预期值.
I've tested this against the examples in the link, and they all return the expected values.
USE Sandbox; GO CREATE FUNCTION ValidateHealthNumber (@HealthNumber varchar(10)) RETURNS TABLE AS RETURN WITH Doubles AS( SELECT CONVERT(tinyint,SUBSTRING(V.HN,O.P,1)) AS HNDigit, CONVERT(tinyint,SUBSTRING(V.HN,O.P,1)) * CASE WHEN O.P % 2 = 0 THEN 1 ELSE 2 END ToAdd FROM (VALUES(@HealthNumber)) V(HN) CROSS APPLY (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) O(P)), Parts AS ( SELECT CONVERT(tinyint,SUBSTRING(CONVERT(varchar(2),ToAdd),1,1)) AS FirstDigit, --We know that the highest value can be 18 (2*9) CONVERT(tinyint,SUBSTRING(CONVERT(varchar(2),ToAdd),2,1)) AS SecondDigit --so no need for more than 2 digits. FROM Doubles) SELECT CASE RIGHT(@HealthNumber, 1) WHEN 10 - RIGHT(SUM(FirstDigit + SecondDigit),1) THEN 1 ELSE 0 END AS IsValid FROM Parts; GO CREATE TABLE #Sample(HealthNumber varchar(10)); INSERT INTO #Sample VALUES ('9876543217'), --Sample ('5322369835'), --Valid ('7089771195'), --Valid ('8108876957'), --Valid ('4395667779'), --Valid ('6983806917'), --Valid ('2790412845'), --not Valid ('5762696912'); --not Valid SELECT * FROM #Sample S CROSS APPLY ValidateHealthNumber(HealthNumber) VHN; GO DROP TABLE #Sample DROP FUNCTION ValidateHealthNumber;如果你不明白这些,请尽管问.
If you don't understand any of this, please do ask.
更多推荐
Azure 数据仓库中的表变量
发布评论