77 lines
2.3 KiB
SQL
77 lines
2.3 KiB
SQL
|
|
SELECT * FROM ads.date_table5;
|
|
|
|
DROP TABLE ads.date_table;
|
|
|
|
|
|
|
|
|
|
CREATE TABLE ads.base_sales(
|
|
day TEXT NOT NULL,
|
|
hour INT,
|
|
user_id BIGINT,
|
|
ts TIMESTAMPTZ,
|
|
amount FLOAT,
|
|
pk TEXT NOT NULL PRIMARY KEY
|
|
);
|
|
|
|
INSERT INTO ads.base_sales VALUES ('2024-08-29',1,222222,'2024-08-29 16:41:19.141528+08',5,'ddd');
|
|
ALTER TABLE ads.base_sales SET (binlog_level = replica);
|
|
INSERT INTO ads.base_sales VALUES ('2024-08-29',2,3333,'2024-08-29 17:44:19.141528+08',100,'aaaaa');
|
|
INSERT INTO ads.base_sales VALUES ('2024-08-29',3,3333,'2024-08-29 17:44:19.141528+08',100,'ccc');
|
|
INSERT INTO ads.base_sales VALUES ('2024-08-29',3,3333,'2024-08-29 17:44:19.141528+08',500,'gg');
|
|
|
|
|
|
SET hg_experimental_enable_hybrid_incremental_mode = true;
|
|
CREATE DYNAMIC TABLE ads.sales_incremental
|
|
WITH (
|
|
refresh_mode='incremental',
|
|
auto_refresh_enable='true',
|
|
incremental_auto_refresh_schd_start_time = 'immediate',
|
|
incremental_auto_refresh_interval = '3 minutes')
|
|
AS
|
|
SELECT day, hour, SUM(amount), COUNT(1)
|
|
FROM ads.base_sales
|
|
GROUP BY day, hour;
|
|
|
|
select * from ads.sales_incremental;
|
|
|
|
CREATE DYNAMIC TABLE ads.q1_full
|
|
WITH (
|
|
refresh_mode='full',
|
|
auto_refresh_enable='true',
|
|
full_auto_refresh_interval='1 minutes'
|
|
-- ,full_guc_hg_computing_resource='serverless',
|
|
-- full_guc_hg_experimental_serverless_computing_required_cores='32'
|
|
)
|
|
AS
|
|
SELECT day, hour, SUM(amount), COUNT(1)
|
|
FROM ads.base_sales
|
|
GROUP BY day, hour;
|
|
|
|
select * from ads.q1_full;
|
|
|
|
DELETE FROM ads.base_sales where hour=1
|
|
|
|
|
|
CREATE DYNAMIC TABLE dt_sales_incremental
|
|
WITH (
|
|
refresh_mode='incremental',
|
|
auto_refresh_enable='true',
|
|
incremental_auto_refresh_schd_start_time='2024-09-15 00:00:00',
|
|
incremental_auto_refresh_interval='5 minutes',
|
|
incremental_guc_hg_computing_resource='serverless',
|
|
incremental_guc_hg_experimental_serverless_computing_required_cores='128')
|
|
AS
|
|
SELECT
|
|
sale_detail.app_id,
|
|
sale_detail.uid,
|
|
product,
|
|
SUM(sale_detail.gmv) AS sum_gmv,
|
|
sale_detail.order_time,
|
|
user_info.province,
|
|
user_info.city
|
|
FROM hm.sale_detail
|
|
INNER JOIN hm.user_info FOR SYSTEM_TIME AS OF PROCTIME()
|
|
ON sale_detail.uid =user_info.uid
|
|
GROUP BY sale_detail.app_id,sale_detail.uid,sale_detail.product,sale_detail.order_time,user_info.province,user_info.city; |