PolarDB这个条 SQL,强行列存的效果还没有行存查询高,怎么回事?SELECT
/+ SET_VAR(cost_threshold_for_imci=0) /
roi.merchant_id,
roi.sku_id,
roi.machine_code,
roi.door_num,
roi.sku_name,
SUM(
CASE
roi.create_type
WHEN 1 THEN
roi.sku_num
WHEN 3 THEN
roi.sku_num
WHEN 2 THEN
- roi.sku_num
WHEN 5 THEN
roi.sku_num
WHEN 6 THEN - roi.sku_num ELSE 0
END
) AS skuSaleNum,
SUM(
CASE
roi.create_type
WHEN 1 THEN
roi.amount
WHEN 3 THEN
roi.amount
WHEN 2 THEN- roi.amount
WHEN 5 THEN
roi.amount
WHEN 6 THEN - roi.amount ELSE 0
END
) AS skuSaleAmount
FROM
r_order_item roi
WHERE
roi.order_id IN (
SELECT
r.order_id
FROM
r_order r
WHERE
r.anomaly_status IN ( 1, 9 )
AND r.close_type IN ( 0, 1, 5, 9, 10 )
AND r.company_id = 314
AND r.merchant_id = 2532
AND r.create_time BETWEEN '2024-04-01 22:07:13.389'
AND '2024-04-18 23:59:59.999'
AND r.amount != 0
)
AND roi.STATUS = 1
AND roi.sku_id NOT IN ( 2, 10 )
GROUP BY
roi.machine_id,
roi.door_num,
roi.sku_id
ORDER BY
skuSaleNum DESC