时间戳

首页 > 解决方案 > 带有聚合错误的 ksqlDB 流连接表

join - 带有聚合错误的 ksqlDB 流连接表

问题描述

当我执行以下 sql 错误时，谁知道如何解决它？非常感谢。

错误消息：类 io.confluent.ksql.execution.expression.tree.LogicalBinaryExpression 不能转换为类 io.confluent.ksql.execution.expression.tree.ComparisonExpression (io.confluent.ksql.execution.expression.tree.LogicalBinaryExpression 和io.confluent.ksql.execution.expression.tree.ComparisonExpression 在加载程序“app”的未命名模块中）

select 
a.mo_order_id,
    a.mo_lot_no,
    b.tenant_id,
    a.line_id,
    substring((TIMESTAMPTOSTRING(a.creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),1,10) as produce_date,
    substring(substring((TIMESTAMPTOSTRING(a.creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),12,12),1,2) as produce_hour,
    (
    case 
    when a.result_code =1  then 1 
    when a.result_code =3  then 1 
    when a.result_code =8  then 1 
    when a.result_code =2 then 2 
    else 0 
    end) cause_type,
    SUM((
    case 
    when ((a.result_code =1) and is_reset = false) then sn_count
    when ((a.result_code =3) and is_reset = false) then sn_count 
    when ((a.result_code =8) and is_reset = false) then sn_count 
    when ((a.result_code =1) and is_reset = true)  then -1*sn_count 
    when (a.result_code = 2) then sn_count else 0 
    end)) stats
from
    STREAM_ORI_SACMES_INSPECTION a
inner join KSQL_TABLE_GP_MO_ORDER b on
    (a.mo_order_id = b.id and (a.result_code =1 or a.result_code =3 or a.result_code =8))
group by 
    a.mo_order_id,
    a.mo_lot_no,
    b.tenant_id,
    a.line_id,
    substring((TIMESTAMPTOSTRING(creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),1,10),
    substring(substring((TIMESTAMPTOSTRING(creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),12,12),1,2),
    (
    case 
    when a.result_code =1  then 1 
    when a.result_code =3  then 1 
    when a.result_code =8  then 1 
    when a.result_code =2 then 2 
    else 0 
    end)
    emit changes;

标签： joinksqldb

解决方案

嗯……看起来像个虫子！

问题是您的加入条件无效：(a.mo_order_id = b.id and (a.result_code =1 or a.result_code =3 or a.result_code =8)).

看起来您的连接条件应该是a.mo_order_id = b.id，并且其他位应该移到一个WHERE子句中。

a.mo_order_id,
    a.mo_lot_no,
    b.tenant_id,
    a.line_id,
    substring((TIMESTAMPTOSTRING(a.creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),1,10) as produce_date,
    substring(substring((TIMESTAMPTOSTRING(a.creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),12,12),1,2) as produce_hour,
    (
    case 
    when a.result_code =1  then 1 
    when a.result_code =3  then 1 
    when a.result_code =8  then 1 
    when a.result_code =2 then 2 
    else 0 
    end) cause_type,
    SUM((
    case 
    when ((a.result_code =1) and is_reset = false) then sn_count
    when ((a.result_code =3) and is_reset = false) then sn_count 
    when ((a.result_code =8) and is_reset = false) then sn_count 
    when ((a.result_code =1) and is_reset = true)  then -1*sn_count 
    when (a.result_code = 2) then sn_count else 0 
    end)) stats
from
    STREAM_ORI_SACMES_INSPECTION a
inner join KSQL_TABLE_GP_MO_ORDER b on
    a.mo_order_id = b.id
where
    a.result_code =1 or a.result_code =3 or a.result_code =8
group by 
    a.mo_order_id,
    a.mo_lot_no,
    b.tenant_id,
    a.line_id,
    substring((TIMESTAMPTOSTRING(creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),1,10),
    substring(substring((TIMESTAMPTOSTRING(creation_time, 'yyyy-MM-dd HH:mm:ss', 'GMT')),12,12),1,2),
    (
    case 
    when a.result_code =1  then 1 
    when a.result_code =3  then 1 
    when a.result_code =8  then 1 
    when a.result_code =2 then 2 
    else 0 
    end)
    emit changes;
join ksqldb

推荐阅读