流计算任务无法创建成功，一直报错，需要帮助

TDuser_n4u2_1416 · 2025 年12 月 24 日 10:36

【TDengine 使用环境】
测试环境

【TDengine 版本】

3.3.8.8

【操作系统以及版本】

【部署方式】非容器部署

【集群节点数】

【集群副本数】

【描述业务影响】

从流水表中做统计，各个维度的，

当前任务：创建每个小时统计一次数量的流计算任务，每个商家放一个子表

【遇到的问题：问题现象及影响】始终无法创建成功，sql各种调整都不行

CREATE stream   str_user_pay_order_mer_per_h
 PERIOD(1h, 5m)
  from tedi_statistics.u_user_pay_order  
  PARTITION  BY   merchant_id 
  INTO tedi_statistics_h.st_merchant_order_h(ts,shop_id,order_count) 
 TAGS(merchant_id bigint)
  SUBTABLE( concat('st_merchant_order_h_' ,merchant_id) )
  AS
SELECT TIMETRUNCATE(now(), 1h, 1)                         ts,
       shop_id,
       count(1)                                        as order_count
from tedi_statistics.u_user_pay_order
where ts >= TIMETRUNCATE(now() - 1h, 1h, 1)
  and ts < TIMETRUNCATE(now(), 1h, 1)
  and trade_status = 2

tedi_statistics_h.st_merchant_order_h 是个超级表，直接改成别的表名也是不行的

【资源配置】

【报错完整截图】（不要大段的粘贴报错代码，论坛直接看报错代码不直观）

TDuser_Apjn_2453 · 2025 年12 月 25 日 03:09

TDuser_n4u2_1416 · 2025 年12 月 25 日 03:41

CREATE STREAM str_user_pay_order_mer_per_h 
PERIOD(1h, 5m)
INTO tedi_statistics_h.st_merchant_order_h(ts, shop_id COMPOSITE KEY, order_count)
OUTPUT_SUBTABLE(concat('st_merchant_order_h_', %%1))
AS
SELECT 
    TIMETRUNCATE(now(), 1h, 1) AS ts,
    shop_id,
    COUNT(1) AS order_count
FROM `tedi-statistics`.u_user_pay_order
WHERE ts >= TIMETRUNCATE(now() - 1h, 1h, 1)
  AND ts < TIMETRUNCATE(now(), 1h, 1)
  AND trade_status = 2
 PARTITION BY merchant_id;

依然不行

TDuser_Apjn_2453 · 2025 年12 月 25 日 03:53

报错发一下

TDuser_n4u2_1416 · 2025 年12 月 25 日 03:55

将子表定义去掉后

CREATE STREAM str_user_pay_order_mer_per_h 
PERIOD(1h, 5m)
INTO tedi_statistics_h.st_merchant_order_h(ts, shop_id COMPOSITE KEY, order_count)
  TAGS(merchant_id bigint)

AS
SELECT 
    TIMETRUNCATE(now(), 1h, 1) AS ts,
    shop_id,
    COUNT(1) AS order_count
FROM `tedi-statistics`.u_user_pay_order
WHERE ts >= TIMETRUNCATE(now() - 1h, 1h, 1)
  AND ts < TIMETRUNCATE(now(), 1h, 1)
  AND trade_status = 2
 PARTITION BY merchant_id,shop_id

报错位置又变成了 near SELECT TIMETRUNCATE ***, 但下面的分组sql直接执行是没问题的

TDuser_n4u2_1416 · 2025 年12 月 25 日 03:56

有子表定义时

执行查询的sql本身可以执行：

TDuser_OEzS_1621 · 2025 年12 月 25 日 05:27

请将 st_merchant_order_h、u_user_pay_order 这个两个超级表的建表语句给一下，我们来创建流看看。

TDuser_n4u2_1416 · 2025 年12 月 25 日 05:53

这个是建表语句

create database  if not exists tedi_statistics;
create database  if not exists tedi_statistics_h;



 -- 订单流水
 CREATE TABLE `tedi_statistics`.u_user_pay_order (
    ts TIMESTAMP,
    order_num BIGINT  COMPOSITE KEY,
    title nchar(32),
    shop_name nchar(64),
    user_id bigint,
    shop_id bigint,
    success TINYINT ,
    trade_status TINYINT ,
    order_amount int,
    receipt_amount int,
    buyer_pay_amount int
) tags(merchant_id bigint );


-- 小时统计
CREATE TABLE tedi_statistics_h.st_merchant_order_h (
     ts TIMESTAMP,           -- 时间戳作为主键
     shop_id BIGINT COMPOSITE  key, -- 店铺ID
     order_count INT,       -- 订单量
     total_amount INT
)
TAGS (
     merchant_id BIGINT
 );

CREATE STREAM str_user_pay_order_mer_per_h 
PERIOD(1h, 5m)
INTO tedi_statistics_h.st_merchant_order_h(ts, shop_id COMPOSITE KEY, order_count)
  TAGS(merchant_id bigint)
OUTPUT_SUBTABLE(concat('st_merchant_order_h_', %%1))
AS
SELECT 
    TIMETRUNCATE(now(), 1h, 1) AS ts,
    shop_id,
    COUNT(1) AS order_count
FROM `tedi_statistics`.u_user_pay_order
WHERE ts >= TIMETRUNCATE(now() - 1h, 1h, 1)
  AND ts < TIMETRUNCATE(now(), 1h, 1)
  AND trade_status = 2
 PARTITION BY merchant_id,shop_id

TDuser_OEzS_1621 · 2025 年12 月 25 日 06:10

TDuser_n4u2_1416:

CREATE stream   str_user_pay_order_mer_per_h
 PERIOD(1h, 5m)
  from tedi_statistics.u_user_pay_order  
  PARTITION  BY   merchant_id 
  INTO tedi_statistics_h.st_merchant_order_h(ts,shop_id,order_count) 
 TAGS(merchant_id bigint)
  SUBTABLE( concat('st_merchant_order_h_' ,merchant_id) )
  AS
SELECT TIMETRUNCATE(now(), 1h, 1)                         ts,
       shop_id,
       count(1)                                        as order_count
from tedi_statistics.u_user_pay_order
where ts >= TIMETRUNCATE(now() - 1h, 1h, 1)
  and ts < TIMETRUNCATE(now(), 1h, 1)
  and trade_status = 2

CREATE stream str_user_pay_order_mer_per_h
PERIOD(1h, 5m)
from tedi_statistics.u_user_pay_order
PARTITION BY merchant_id
INTO tedi_statistics_h.st_merchant_order_h(ts,shop_id,order_count)
TAGS(merchant_id bigint)
SUBTABLE( concat(‘st_merchant_order_h_’ ,merchant_id) )
AS
SELECT TIMETRUNCATE(now(), 1h, 1) ts,
shop_id,
count(1) as order_count
from tedi_statistics.u_user_pay_order
where ts >= TIMETRUNCATE(now() - 1h, 1h, 1)
and ts < TIMETRUNCATE(now(), 1h, 1)
and trade_status = 2:

这个流语句有几个错误：
1、SUBTABLE 应该是 OUTPUT_SUBTABLE；
2、OUTPUT_SUBTABLE 这个方法应该在 (ts,shop_id,order_count) 前面；
3、AS select 中有 count(1) , 这是一个聚合函数，其他的 shop_id 又是简单的投影，这两个是冲突的。

TDuser_n4u2_1416 · 2025 年12 月 25 日 06:29

我新贴的都是改了的，分组中有shop_id

CREATE STREAM str_user_pay_order_mer_per_h 
PERIOD(1h, 5m)
INTO tedi_statistics_h.st_merchant_order_h(ts, shop_id COMPOSITE KEY, order_count)
  TAGS(merchant_id bigint)
OUTPUT_SUBTABLE(concat('st_merchant_order_h_', %%1))
AS
SELECT 
    TIMETRUNCATE(now(), 1h, 1) AS ts,
    shop_id,
    COUNT(1) AS order_count
FROM `tedi_statistics`.u_user_pay_order
WHERE ts >= TIMETRUNCATE(now() - 1h, 1h, 1)
  AND ts < TIMETRUNCATE(now(), 1h, 1)
  AND trade_status = 2
 PARTITION BY merchant_id,shop_id

TDuser_n4u2_1416 · 2025 年12 月 25 日 06:33

去掉shop_id ，也是有错误的

CREATE STREAM str_user_pay_order_mer_per_h 
PERIOD(1h, 5m)

INTO tedi_statistics_h.st_merchant_order_h1
  OUTPUT_SUBTABLE(concat('st_merchant_order_h_', %%1))
  (ts,shop_id COMPOSITE KEY,  order_count)
  TAGS(merchant_id bigint)

AS
SELECT 
    TIMETRUNCATE(now(), 1h, 1) AS ts,
    shop_id,
    COUNT(1) AS order_count
FROM `tedi_statistics`.u_user_pay_order1
WHERE ts >= TIMETRUNCATE(now() - 1h, 1h, 1)
  AND ts < TIMETRUNCATE(now(), 1h, 1)
  AND trade_status = 2
 PARTITION BY merchant_id,shop_id

TDuser_OEzS_1621 · 2025 年12 月 25 日 10:16

CREATE STREAM str_user_pay_order_mer_per_h
PERIOD(1h, 5m) from tedi_statistics.u_user_pay_order PARTITION BY merchant_id
INTO tedi_statistics_h.st_merchant_order_h OUTPUT_SUBTABLE(concat(‘st_merchant_order_h_’, cast(merchant_id as binary))) (ts, shop_id, order_count,total_amount)
TAGS(merchant_id bigint as merchant_id)
AS
SELECT
TIMETRUNCATE(now(), 1h, 1) AS ts,
shop_id,
order_amount as order_count,
receipt_amount as total_amount
FROM tedi_statistics.u_user_pay_order
WHERE ts >= TIMETRUNCATE(now() - 1h, 1h, 1)
AND ts < TIMETRUNCATE(now(), 1h, 1)
AND trade_status = 2
PARTITION BY merchant_id,shop_id;

这个可以创建成功了。
就是有一个功能：输出的流结果与已有超级表必须全部列字段匹配，还不支持只输出部分指定列。

TDuser_n4u2_1416 · 2025 年12 月 25 日 10:33

好的，感谢，我去试试

system · 2026 年1 月 24 日 10:34

此话题已在最后回复的 30 天后被自动关闭。不再允许新回复。