Snowflake Problem: 使用日期及其属性填充表格

huangapple go评论73阅读模式
英文:

Snowflake Problem: Populate Table with Dates and its Attributes

问题

Here's the translation of the code part you provided:

我有一个问题陈述:
编写一个存储过程,用于填充一个带有特定日期属性的表格。数据将被填充一年。例如,将日期14-07-2020作为输入参数传递,然后存储过程将为2020年内所有日期填充这些属性。此表格的主键将是日期列。
约束:不能使用多个插入语句

表格的结构如图所示。

我针对这个问题陈述的Snowflake代码实现如下:

create or replace table DateTimeDimensions (SKDate varchar(50), KeyDate varchar(50), Date varchar(50), CalendarDay int, CalendarMonth int, CalendarQuarter int, CalendarYear int, DayNameLog varchar(50), DayNameShort varchar(50), DayNumOfWeek int, DayNumofYear int, DaySuffix varchar(10), FiscalWeek int, FiscalPeriod int, FiscalQuarter int, FiscalYear int, FiscalYear_Period int);
select * from DateTimeDimensions
truncate table DateTimeDimensions
drop table DateTimeDimensions

CREATE OR REPLACE PROCEDURE uspPopulateDateTimeTable(startDate TIMESTAMP)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
{
    var currentDate = startDate;
    var endDate = DATEADD('YEAR', 1, startDate);

    while (currentDate < endDate) {
        var SKDate = currentDate.format('YYYYMMDD');
        var KeyDate = currentDate.format('MM/DD/YYYY');
        var Date = currentDate.format('MM/DD/YYYY');
        var CalendarDay = currentDate.getDate();
        var CalendarMonth = currentDate.getMonth() + 1;
        var CalendarQuarter = Math.ceil(CalendarMonth / 3);
        var CalendarYear = currentDate.getFullYear();
        var DayNameLong = currentDate.toLocaleString('en-US', { weekday: 'long' });
        var DayNameShort = currentDate.toLocaleString('en-US', { weekday: 'short' });
        var DayNumberOfWeek = currentDate.getDay() + 1;
        var DayNumberOfYear = Math.ceil((currentDate - new Date(currentDate.getFullYear(), 0, 1)) / 86400000);
        var DaySuffix = CalendarDay + (CalendarDay % 10 === 1 && CalendarDay !== 11 ? 'st' : (CalendarDay % 10 === 2 && CalendarDay !== 12 ? 'nd' : (CalendarDay % 10 === 3 && CalendarDay !== 13 ? 'rd' : 'th')));
        var FiscalWeek = currentDate.getWeek(5);
        var FiscalPeriod = CalendarMonth;
        var FiscalQuarter = CalendarQuarter;
        var FiscalYear = CalendarYear;
        var FiscalYear_Period = CalendarYear + padLeft(CalendarMonth.toString(), 2, '0');

        var sqlStatement = 'INSERT INTO DateTimeDimensions (SKDate, KeyDate, Date, CalendarDay, CalendarMonth, CalendarQuarter, CalendarYear, DayNameLong, DayNameShort, DayNumberOfWeek, DayNumberOfYear, DaySuffix, FiscalWeek, FiscalPeriod, FiscalQuarter, FiscalYear, FiscalYear_Period) ' +
            'VALUES (\'' + SKDate + '\', \'' + KeyDate + '\', \'' + Date + '\', ' + CalendarDay + ', ' + CalendarMonth + ', ' + CalendarQuarter + ', ' + CalendarYear + ', \'' + DayNameLong + '\', \'' + DayNameShort + '\', ' + DayNumberOfWeek + ', ' + DayNumberOfYear + ', \'' + DaySuffix + '\', ' + FiscalWeek + ', ' + FiscalPeriod + ', ' + FiscalQuarter + ', ' + FiscalYear + ', \'' + FiscalYear_Period + '\');';

        snowflake.execute({ sqlText: sqlStatement });

        currentDate = DATEADD('DAY', 1, currentDate);
    }

    return 'DateTimeDimensions表格成功填充。';
}
$$;

CALL uspPopulateDateTimeTable('2020-07-14 00:00:00');

I hope this helps. If you have any more questions or need further assistance, please feel free to ask.

英文:

I have a problem statement:
Write a Stored Procedure that populates a table with certain date attributes. The data would be populated for 1 year. For example the date 14-07-2020 is passed as an input parameter, then the stored procedure will populate those attributes for all the dates present within the year 2020. The primary key for this table would be date column.
Constraint: More than one insert statement cannot be used

Snowflake Problem: 使用日期及其属性填充表格|

The structure of table is given in the image.

My implementation of snowflake code for the problem statement is:

create or replace table DateTimeDimensions (SKDate varchar(50), KeyDate varchar(50), Date varchar(50), CalendarDay int, CalendarMonth int, CalendarQuarter int, CalendarYear int, DayNameLog varchar(50), DayNameShort varchar(50), DayNumOfWeek int, DayNumofYear int, DaySuffix varchar(10), FiscalWeek int, FiscalPeriod int, FiscalQuarter int, FiscalYear int, FiscalYear_Period int);
select * from DateTimeDimensions
truncate table DateTimeDimensions
drop table DateTimeDimensions

CREATE OR REPLACE PROCEDURE uspPopulateDateTimeTable(startDate TIMESTAMP)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
{
    var currentDate = startDate;
    var endDate = DATEADD(&#39;YEAR&#39;, 1, startDate);

    while (currentDate &lt; endDate) {
        var SKDate = currentDate.format(&#39;YYYYMMDD&#39;);
        var KeyDate = currentDate.format(&#39;MM/DD/YYYY&#39;);
        var Date = currentDate.format(&#39;MM/DD/YYYY&#39;);
        var CalendarDay = currentDate.getDate();
        var CalendarMonth = currentDate.getMonth() + 1;
        var CalendarQuarter = Math.ceil(CalendarMonth / 3);
        var CalendarYear = currentDate.getFullYear();
        var DayNameLong = currentDate.toLocaleString(&#39;en-US&#39;, { weekday: &#39;long&#39; });
        var DayNameShort = currentDate.toLocaleString(&#39;en-US&#39;, { weekday: &#39;short&#39; });
        var DayNumberOfWeek = currentDate.getDay() + 1;
        var DayNumberOfYear = Math.ceil((currentDate - new Date(currentDate.getFullYear(), 0, 1)) / 86400000);
        var DaySuffix = CalendarDay + (CalendarDay % 10 === 1 &amp;&amp; CalendarDay !== 11 ? &#39;st&#39; : (CalendarDay % 10 === 2 &amp;&amp; CalendarDay !== 12 ? &#39;nd&#39; : (CalendarDay % 10 === 3 &amp;&amp; CalendarDay !== 13 ? &#39;rd&#39; : &#39;th&#39;)));
        var FiscalWeek = currentDate.getWeek(5);
        var FiscalPeriod = CalendarMonth;
        var FiscalQuarter = CalendarQuarter;
        var FiscalYear = CalendarYear;
        var FiscalYear_Period = CalendarYear + padLeft(CalendarMonth.toString(), 2, &#39;0&#39;);

        var sqlStatement = &#39;INSERT INTO DateTimeDimensions (SKDate, KeyDate, Date, CalendarDay, CalendarMonth, CalendarQuarter, CalendarYear, DayNameLong, DayNameShort, DayNumberOfWeek, DayNumberOfYear, DaySuffix, FiscalWeek, FiscalPeriod, FiscalQuarter, FiscalYear, FiscalYear_Period) &#39; +
            &#39;VALUES (\&#39;&#39; + SKDate + &#39;\&#39;, \&#39;&#39; + KeyDate + &#39;\&#39;, \&#39;&#39; + Date + &#39;\&#39;, &#39; + CalendarDay + &#39;, &#39; + CalendarMonth + &#39;, &#39; + CalendarQuarter + &#39;, &#39; + CalendarYear + &#39;, \&#39;&#39; + DayNameLong + &#39;\&#39;, \&#39;&#39; + DayNameShort + &#39;\&#39;, &#39; + DayNumberOfWeek + &#39;, &#39; + DayNumberOfYear + &#39;, \&#39;&#39; + DaySuffix + &#39;\&#39;, &#39; + FiscalWeek + &#39;, &#39; + FiscalPeriod + &#39;, &#39; + FiscalQuarter + &#39;, &#39; + FiscalYear + &#39;, \&#39;&#39; + FiscalYear_Period + &#39;\&#39;);&#39;;

        snowflake.execute({ sqlText: sqlStatement });

        currentDate = DATEADD(&#39;DAY&#39;, 1, currentDate);
    }

    return &#39;DateTimeDimensions table populated successfully.&#39;;
}
$$;

CALL uspPopulateDateTimeTable(&#39;2020-07-14 00:00:00&#39;);

But it is giving me error as:

JavaScript execution error: Uncaught ReferenceError: startDate is not defined in USPPOPULATEDATETIMETABLE at &#39; var currentDate = startDate;&#39; position 22 stackstrace: USPPOPULATEDATETIMETABLE line: 3

Basically the error says that

    var currentDate = startDate;

is not working because startDate is not defined.

I have searched online and read the documentation properly but can't find out the solution for this error. Help me in solving this.

I tried to search thoroughly through the Snowflake documentation for writing stored procedure using Javascript and SQL but it was of no help.
Again and Again same error was occuring.
I tried to implement using SQL sripting instead of Javascript but it gave even more error for the same logic.

I have not tried it using CTE because I think it will be more complicated.

答案1

得分: 1

创建或替换存储过程 uspPopulateDateTimeTable("startDate" TIMESTAMP)
返回 STRING
语言 JAVASCRIPT
AS
$$
{
    var currentDate = startDate;
    return currentDate;
}
$$
;

调用 uspPopulateDateTimeTable('2023-05-18');

这会正常工作。

英文:
CREATE OR REPLACE PROCEDURE uspPopulateDateTimeTable(&quot;startDate&quot; TIMESTAMP)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
{
    var currentDate = startDate;
    return currentDate;
}
$$
;

call uspPopulateDateTimeTable(&#39;2023-05-18&#39;);

this would work

答案2

得分: 0

你必须以大写字母指定存储过程参数的名称。

var currentDate = STARTDATE;

英文:

You have to specify the stored procedure argument names in uppercase.

    var currentDate = STARTDATE;

huangapple
  • 本文由 发表于 2023年5月17日 20:51:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76272327.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定