HolgerWang1999 commented on issue #35844:
URL: https://github.com/apache/doris/issues/35844#issuecomment-2149030223
使用了SUBSTRING的子查询源表的建表语句：
CREATE TABLE digital_marketing.ods_mysql_vesync_stat_operation_data_stat_fd (
`id` INT COMMENT 'id',
`metric_name` STRING,
`metric_value` STRING,
`stat_value` STRING,
`stat_date` DATE,
`create_time` DATETIME COMMENT '数据创建时间',
`update_time` DATETIME COMMENT '数据修改时间'
)
UNIQUE KEY (id)
COMMENT "EDM-运营指标表"
DISTRIBUTED BY HASH(id) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default:2",
"enable_unique_key_merge_on_write" = "true"
);
对metric_name进行SUBSTRING并进行关联的示例SQL：
SELECT
*
FROM
(
SELECT
tmp.task_id,
tmp.metric_value,
tmp.stat_value
FROM
(
SELECT
SUBSTRING(metric_name, 12) AS task_id,
metric_value,
stat_value,
RANK() OVER (
PARTITION BY
metric_name
ORDER BY
stat_date DESC
) AS rk
FROM
digital_marketing.ods_mysql_vesync_stat_operation_data_stat_fd
WHERE
INSTR(metric_name, 'EDM:mailId:') > 0
AND metric_value = 'relatedOrderQuantity'
) tmp
WHERE
tmp.rk = 1
) AS ods1
LEFT JOIN (
SELECT
tmp.task_id,
tmp.metric_value,
tmp.stat_value
FROM
(
SELECT
SUBSTRING(metric_name, 12) AS task_id,
metric_value,
stat_value,
RANK() OVER (
PARTITION BY
metric_name
ORDER BY
stat_date DESC
) AS rk
FROM
digital_marketing.ods_mysql_vesync_stat_operation_data_stat_fd
WHERE
INSTR(metric_name, 'EDM:mailId:') > 0
AND metric_value = 'relatedSales'
) tmp
WHERE
tmp.rk = 1
) AS ods2 ON ods1.task_id = ods2.task_id;
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
