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;