- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在尝试将此 pl/pgSQL 函数转换为在 Snowflake 上工作。不幸的是,我刚开始使用 Snowflake,无法正确转换它。 Snowflake 计划在年底前支持 pgSQL 查询,不幸的是,目前还没有。
这里是一个快速介绍,让您了解我的表存储什么以及该功能的作用。我有三张 table 。您可以在这个问题的底部找到所有表和示例数据的 DDL 语句。
我每天一次将数据导入事件表。我们感兴趣的事件是设备类型为 1 和 2(进入/退出)的事件。然后我运行我的函数,它计算具有相同 card_nr 的事件之间的正确持续时间。之后,我将这些持续时间导入我的持续时间表并更新属性表。
这是事件的一个例子:
下面是调用函数后的持续时间示例:
我需要涵盖的最重要的事情是:
durationLimitDate = (Max(event_time) - durationLimitDays))
函数
CREATE OR REPLACE FUNCTION calculateduration() RETURNS void AS $function$
WITH cte AS (SELECT e.id, e.card_nr, e.event_time, e.ticket_type, e.manufacturer, e.carpark_id, e.device_type,
ROW_NUMBER() OVER (ORDER BY e.card_nr, e.carpark_id, e.event_time, e.device_type) AS rn
FROM events e
LEFT JOIN durations d ON d.event_id_arrival = e.id OR d.event_id_departure = e.id
WHERE e.event_time >= (SELECT PROP_VALUE::timestamp FROM properties WHERE prop_key = 'DURATION.LIMIT.DATE')
AND e.device_type IN (1, 2)
AND event_type = 2
AND e.manufacturer LIKE 'XX%'
AND d.id IS NULL)
INSERT INTO durations (id, odb_created_at, event_id_arrival, event_id_departure,
event_time_arrival, event_time_departure,
card_nr, ticket_type, duration, manufacturer, carpark_id)
SELECT nextval('durations_id_seq'),
current_timestamp,
arrived_entry.id,
departed_entry.id,
arrived_entry.event_time,
departed_entry.event_time,
arrived_entry.card_nr,
arrived_entry.ticket_type,
date_part('epoch', departed_entry.event_time::timestamp - arrived_entry.event_time::timestamp),
arrived_entry.manufacturer,
arrived_entry.carpark_id
FROM (SELECT * FROM cte WHERE cte.device_type = 1) AS arrived_entry
INNER JOIN (SELECT * FROM cte WHERE cte.device_type = 2) AS departed_entry ON arrived_entry.card_nr = departed_entry.card_nr
AND arrived_entry.carpark_id = departed_entry.carpark_id
AND arrived_entry.rn + 1 = departed_entry.rn;
UPDATE properties
SET PROP_VALUE = (SELECT (MAX(event_time) - ((SELECT PROP_VALUE FROM properties WHERE prop_key = 'DURATION.LIMIT.DAYS') ||' day')::interval) FROM events WHERE event_time >= (SELECT PROP_VALUE::timestamp FROM properties WHERE prop_key = 'DURATION.LIMIT.DATE'))
WHERE PROP_KEY ='DURATION.LIMIT.DATE';
$function$
LANGUAGE sql;
DDL 脚本
-- events
CREATE TABLE IF NOT EXISTS events (
id bigint NOT NULL autoincrement start 1 increment 1 PRIMARY KEY,
odb_created_at timestamp without time zone NOT NULL,
event_time timestamp without time zone NOT NULL,
device_type integer NOT NULL,
event_type integer NOT NULL,
ticket_type integer NOT NULL,
card_nr character varying(100),
count integer DEFAULT 1 NOT NULL,
manufacturer character varying(200),
carpark_id bigint
);
-- durations
CREATE TABLE IF NOT EXISTS durations (
id bigint NOT NULL autoincrement start 1 increment 1 PRIMARY KEY,
odb_created_at timestamp without time zone NOT NULL,
event_id_arrival bigint,
event_id_departure bigint,
event_time_arrival timestamp without time zone,
event_time_departure timestamp without time zone,
card_nr character varying(100),
ticket_type integer,
duration integer,
manufacturer character varying(200),
carpark_id bigint
);
--properties
create or replace TABLE PROPERTIES (
PROP_KEY VARCHAR(80) NOT NULL,
PROP_VALUE VARCHAR(250),
primary key (PROP_KEY)
);
示例数据:
INSERT INTO properties (prop_key,prop_value) VALUES
('DURATION.LIMIT.DAYS','30'),
('DURATION.LIMIT.DATE','2021-08-01 00:00:00.00');
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188160996, '2021-10-02 04:28:26.338', '2021-10-01 09:14:41.32', 1, 2, 11, '03998988030897300007782', 1, 'XX', 1852);
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188160790, '2021-10-02 04:28:26.248', '2021-10-01 09:31:10.94', 2, 2, 11, '03998988030897300007782', 1, 'XX', 1852);
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188146489, '2021-10-02 04:26:55.069', '2021-10-01 10:03:01.57', 1, 2, 500, '01479804030429500089598', 1, 'XX', 1563);
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188146069, '2021-10-02 04:26:54.852', '2021-10-01 11:49:58.45', 2, 2, 500, '01479804030429500089598', 1, 'XX', 1563);
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188161161, '2021-10-02 04:28:26.372', '2021-10-01 18:44:33.62', 1, 2, 11, '03998988030897300007782', 1, 'XX', 1852);
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188160950, '2021-10-02 04:28:26.329', '2021-10-01 18:45:51.903', 2, 2, 11, '03998988030897300007782', 1, 'XX', 1852);
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188161227, '2021-10-02 04:28:26.374', '2021-10-01 23:21:18.58', 1, 2, 11, '04139733030897300003136', 1, 'XX', 1852);
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188160974, '2021-10-02 04:28:26.334', '2021-10-01 23:24:03.29', 2, 2, 11, '04139733030897300003136', 1, 'XX', 1852);
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188239864, '2021-10-03 04:24:43.345', '2021-10-02 06:49:55.97', 1, 2, 11, '01719400030897300061410', 1, 'XX', 1852);
INSERT INTO public.events (id, odb_created_at, event_time, device_type, event_type, ticket_type, card_nr, count, manufacturer, carpark_id) VALUES(188239649, '2021-10-03 04:24:43.308', '2021-10-02 07:02:08.72', 2, 2, 11, '01719400030897300061410', 1, 'XX', 1852);
谢谢!
独特的测试
CREATE TABLE IF NOT EXISTS test (
id bigint NOT NULL AUTOINCREMENT PRIMARY KEY -- Check the syntax!
, odb_created_at timestamp without time zone NOT NULL
, event_time timestamp without time zone NOT NULL
, device_type integer NOT NULL
, event_type integer NOT NULL
, ticket_type integer NOT NULL
, card_nr character varying(100)
, count integer DEFAULT 1 NOT NULL
, manufacturer character varying(200)
, carpark_id bigint
, UNIQUE (card_nr, event_time) -- NATURAL KEY
);
INSERT INTO EUILOGS_DEV.ILOGS.TEST (ODB_CREATED_AT, EVENT_TIME, DEVICE_TYPE, EVENT_TYPE, TICKET_TYPE, CARD_NR, COUNT, MANUFACTURER, CARPARK_ID)
VALUES(current_timestamp(), '2021-01-01 15:00:00.000', 1, 1, 1, 'CARD1', 1, 'MAN1', 1);
INSERT INTO EUILOGS_DEV.ILOGS.TEST (ODB_CREATED_AT, EVENT_TIME, DEVICE_TYPE, EVENT_TYPE, TICKET_TYPE, CARD_NR, COUNT, MANUFACTURER, CARPARK_ID)
VALUES(current_timestamp(), '2021-01-01 15:00:00.000', 1, 1, 1, 'CARD1', 1, 'MAN1', 1);
最佳答案
您需要使用序列来生成自动编号: https://docs.snowflake.com/en/user-guide/querying-sequences.html
所以先创建一个sequence来使用它:
create or replace sequence seq1;
然后使用下面的函数:
create or replace procedure calculateduration()
RETURNS string
LANGUAGE JAVASCRIPT
AS $$
var query1 =
`
INSERT INTO durations (id, odb_created_at, event_id_arrival, event_id_departure,
event_time_arrival, event_time_departure,
card_nr, ticket_type, duration, manufacturer, carpark_id)
WITH cte AS (
SELECT e.id, e.card_nr, e.event_time, e.ticket_type, e.manufacturer, e.carpark_id, e.device_type,
ROW_NUMBER() OVER (ORDER BY e.card_nr, e.carpark_id, e.event_time, e.device_type) AS rn
FROM events e
LEFT JOIN durations d ON d.event_id_arrival = e.id OR d.event_id_departure = e.id
WHERE e.event_time >= (SELECT PROP_VALUE::timestamp FROM properties WHERE prop_key = 'DURATION.LIMIT.DATE')
AND e.device_type IN (1, 2)
AND event_type = 2
AND e.manufacturer LIKE 'XX%'
AND d.id IS NULL
)
SELECT
seq1.nextval,
current_timestamp(),
arrived_entry.id,
departed_entry.id,
arrived_entry.event_time,
departed_entry.event_time,
arrived_entry.card_nr,
arrived_entry.ticket_type,
timestampdiff(second, arrived_entry.event_time, departed_entry.event_time),
arrived_entry.manufacturer,
arrived_entry.carpark_id
FROM (SELECT * FROM cte WHERE cte.device_type = 1) AS arrived_entry
INNER JOIN (SELECT * FROM cte WHERE cte.device_type = 2) AS departed_entry
ON arrived_entry.card_nr = departed_entry.card_nr
AND arrived_entry.carpark_id = departed_entry.carpark_id
AND arrived_entry.rn + 1 = departed_entry.rn
`;
snowflake.execute({ sqlText: query1 });
var query2 = "SELECT PROP_VALUE FROM properties WHERE prop_key = 'DURATION.LIMIT.DAYS'";
var stmt = snowflake.createStatement({ sqlText: query2 });
var resultSet = stmt.execute();
resultSet.next();
var prop_value = resultSet.getColumnValue(1);
var query3 =
`
UPDATE properties
SET PROP_VALUE = (
SELECT dateadd(day, -1 * ${prop_value}, MAX(event_time)) FROM events
WHERE event_time >= (
SELECT PROP_VALUE::timestamp FROM properties WHERE prop_key = 'DURATION.LIMIT.DATE'
)
)
WHERE PROP_KEY ='DURATION.LIMIT.DATE';
`
stmt = snowflake.createStatement({ sqlText: query3 });
stmt.execute();
return 'true';
$$;
然后调用过程:
call calculateduration();
代码很简单,没有太多验证和检查,但它应该可以完成您需要的工作。
关于postgresql - 将 Postgres 查询翻译为与 Snowflake 兼容,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/69660453/
翻译自官方wiki: https://github.com/facebook/rocksdb/wiki/Write-Stalls 转载请注明出处: https://www.cnblogs.c
译者注:在微服务架构设计,构建API和服务间通信技术选型时,对 REST 和 gRPC 的理解和应用还存在知识盲区,近期看到国外的这篇文章: A detailed comparison of
rocksdb调试指引 翻译自官方wiki: https://github.com/facebook/rocksdb/wiki/RocksDB-Tuning-Guide 转载请注明出处: h
传统的ASP.NET Web Forms是一个非常好的主意,但现实需求非常复杂。随着时间的推移,现实世界的项目暴露出Web Forms的一些不足之处: “沉重的”视图状态:现实中在http请求之间
翻译自:Top 10 questions of Java Strings 简单地说,”==”测试两个字符串的引用是否相同,equals()测试两个字符串的值是否相同。除非你希望检
你好,今天我要和大家分享一些东西,举例来说这个在JavaScript中用的很多。我要讲讲回调(callbacks)。你知道什么时候用,怎么用这个吗?你真的理解了它在java环境中的用法了吗?当我也问
Java多线程面试问题 1. 进程和线程之间有什么不同? 一个进程是一个独立(self contained)的运行环境,它可以被看作一个程序或者一个应用。而线程是在进程中执行的一个
原文: [A Dive into .Net 8 Native AOT and Efficient Web Development] 作者: [sharmila subbiah] 引言 随着 .NE
这是Fiddle 是否可以在 angular-translate 中检查其他语言的键值是否可用,然后它可以从其他语言中提取该键值? 就像在示例中,我有英语和西类牙语。并且一个键值(例如“CONFIRM
我希望能够使用 $this->__('String to translate')在外部脚本中。我该怎么做呢? Magento 版本 1.5.1.0 . 最佳答案 我认为设置语言环境的正确方法是: Ma
我有一个开关小部件,它使用自定义数据属性值来标记自己。 .switch.switch-text .switch-label::before { right: 1px; color: #c2cf
是否有人遇到过这样的情况:用 Java 编写并由(例如)法国程序员编写的现有代码库必须转换为英语程序员可以理解的代码?这里的问题是变量/方法/类名称、注释等都将采用该特定语言。 现在有可用的自动化解决
维基百科和其他一些网站将解释器描述为将代码从某种高级语言翻译成某种低级语言的翻译器。然而,有很多解释,包括在 stackoverflow 中,它说解释器直接执行作为输入的指令,而无需事先转换。那么解释
我想将基本动画应用于自定义单元格中的某些元素,例如标签、图像:特别是,我想让这些动画在我触摸单元格内部时也启动。我是初学者,我只学会了使用 animateWithDuration 和 transiti
这个问题在这里已经有了答案: NSDateFormatter and current language in iOS11 (5 个回答) 已关闭 3 年前。 当使用这样的 DateComponentF
我想在点击 var about 时移动 div.willshow。但我单击那个 btn,只有它获得类 active。然后我再次单击那个 btn 它失去了类。如果我再点击一次,每项任务都无法正常工作。
我想要一个按钮在悬停时向下移动几个像素,但它又回来了。当您还在上面徘徊时,它不应该留在原处吗? Email Me .btn {background: #2ecc71; padding: .5em 1e
在我的应用程序中,我想添加功能将页面翻译为用户在浏览器中设置的所有语言,如果没有可用的语言,则翻译为默认英语...问题是浏览器与语言支持不一致。我找到了一个解决方法,我对一些返回用户语言的 Web 服
我的应用程序有一个 Help.htm 文件,用谷歌翻译翻译得相当好。我想将菜单项标记为“请勿翻译”,但我发现并尝试过的 HTML 标签都不起作用。对于以下内容,我使用了谷歌翻译网站 - 它翻译了我没想
我有以下代码: span { width:200px; height:100px; background-color:red; border:1px solid black; } span.c2 {
我是一名优秀的程序员,十分优秀!