开发者社区 > PolarDB开源 > PolarDB 分布式版 > 正文

PolarDB这个问题怎么解决? max没有排序语义 sql语义变了

PolarDB这个问题怎么解决? max没有排序语义 sql语义变了,改成这种窗口函数实现 可以走列存 也非常慢 直接把ap节点cpu拉满了,SELECT ep.product_id, p.products_name AS products_name_m, p.products_url_name, ep.enterprise_id, ent.enterprise_name_en AS enterprise_name
, ent.url_name AS enterprise_url_name, ep.specification, DATE_FORMAT(ep.price_date, '%Y-%m-%d') AS price_date
, ep.price_unit, ep.price AS current_price
, (
SELECT tmp.price
FROM (
SELECT ep2.price, ROW_NUMBER() OVER (PARTITION BY ep2.product_id ORDER BY ep2.price_date DESC) AS rank_no
FROM echemi_db_v0_3.t_zy_enterprise_price ep2
LEFT JOIN t_zy_enterprise_price ep
ON ep2.product_id = ep.product_id
AND ep2.enterprise_id = ep.enterprise_id
WHERE IFNULL(ep2.delflag, '') <> '1'
AND ep2.price_date < ep.price_date
AND IFNULL(ep2.price_date, '') != ''
AND IFNULL(ep2.price, '') != ''
AND IFNULL(ep2.delflag, '') != '1'
) tmp
WHERE tmp.rank_no = 1
) AS previous_price, 0 AS subscribe_flag
FROM t_zy_enterprise_price ep
INNER JOIN t_e_products p
ON p.products_id = ep.product_id
AND p.information_flag = '1'
AND IFNULL(p.delflag, '0') != '1'
INNER JOIN t_zy_enterprise ent
ON ent.id = ep.enterprise_id
AND IFNULL(ent.delflag, '0') != '1'
INNER JOIN (
SELECT ep3.product_id, ep3.enterprise_id, MAX(ep3.price_date) AS max_price_date
FROM t_zy_enterprise_price ep3
GROUP BY ep3.product_id, ep3.enterprise_id
) pimax
ON pimax.product_id = ep.product_id
AND pimax.enterprise_id = ep.enterprise_id
AND pimax.max_price_date = ep.price_date
WHERE ep.delflag = '0'
ORDER BY subscribe_flag DESC, ep.price_date DESC, ep.update_date DESC, ep.id
LIMIT 5

展开
收起
三分钟热度的鱼 2023-12-28 19:48:02 40 0
1 条回答
写回答
取消 提交回答
  • 那得用window改,SELECT
    tmp1.*,
    (
    SELECT
    tmp.price
    FROM
    (
    SELECT
    ep2.price,
    ROW_NUMBER() OVER (
    PARTITION BY ep2.product_id
    ORDER BY
    ep2.price_date DESC
    ) AS rank_no
    FROM
    echemi_db_v0_3.t_zy_enterprise_price ep2
    LEFT JOIN t_zy_enterprise_price ep ON ep2.product_id = ep.product_id
    AND ep2.enterprise_id = ep.enterprise_id
    WHERE
    IFNULL(ep2.delflag, '') <> '1'
    AND ep2.price_date < ep.price_date
    AND IFNULL(ep2.price_date, '') != ''
    AND IFNULL(ep2.price, '') != ''
    AND IFNULL(ep2.delflag, '') != '1'
    ) tmp
    WHERE
    tmp.rank_no = 1
    ) AS previous_price,
    0 AS subscribe_flag
    FROM
    (
    SELECT
    ep.product_id,
    p.products_name AS products_name_m,
    p.products_url_name,
    ep.enterprise_id,
    ent.enterprise_name_en AS enterprise_name,
    ent.url_name AS enterprise_url_name,
    ep.specification,
    DATE_FORMAT(ep.price_date, '%Y-%m-%d') AS price_date,
    ep.price_unit,
    ep.price AS current_price,
    ep.price_date as pd,
    ep.update_date as ud,
    ep.id as id
    FROM
    t_zy_enterprise_price ep
    INNER JOIN t_e_products p ON p.products_id = ep.product_id
    AND p.information_flag = '1'
    AND IFNULL(p.delflag, '0') != '1'
    INNER JOIN t_zy_enterprise ent ON ent.id = ep.enterprise_id
    AND IFNULL(ent.delflag, '0') != '1'
    INNER JOIN (
    SELECT
    ep3.product_id,
    ep3.enterprise_id,
    MAX(ep3.price_date) AS max_price_date
    FROM
    t_zy_enterprise_price ep3
    GROUP BY
    ep3.product_id,
    ep3.enterprise_id
    ) pimax ON pimax.product_id = ep.product_id
    AND pimax.enterprise_id = ep.enterprise_id
    AND pimax.max_price_date = ep.price_date
    WHERE
    ep.delflag = '0'
    ORDER BY
    subscribe_flag DESC,
    ep.price_date DESC,
    ep.update_date DESC,
    ep.id
    LIMIT
    5
    ) tmp1
    ORDER BY
    subscribe_flag DESC,
    tmp1.pd DESC,
    tmp1.ud DESC,
    tmp1.id
    LIMIT
    5
    试试这个,先做LIMIT 5减少下window的计算量,也可以试试把
    SELECT ep2.price, ep2.price_date FROM t_zy_enterprise_price ep2 ORDER BY ep2.price_date DESC, ep2.price desc limit 1;
    改为
    select max(ep2.price) FROM t_zy_enterprise_price ep2 where ep2.price_date = (SELECT max(price_date) from t_zy_enterprise_price);此回答整理自钉群“PolarDB专家面对面 - HTAP(列存索引)”

    2023-12-28 19:57:41
    赞同 展开评论 打赏

PolarDB 分布式版 (PolarDB for Xscale,简称“PolarDB-X”) 是阿里云自主设计研发的高性能云原生分布式数据库产品,为用户提供高吞吐、大存储、低延时、易扩展和超高可用的云时代数据库服务。

相关电子书

更多
SQL Server在电子商务中的应用与实践 立即下载
GeoMesa on Spark SQL 立即下载
原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili 立即下载