【已解决】对于不更新表的每次查询结果都不一样

Viewed 40

表结构

CREATE TABLE database.`table_1d` (
  `stat_date` date NOT NULL COMMENT '统计时间_天',
  `adset_id` varchar(512) NOT NULL COMMENT '广告组id',
  `spend_amt_1d` DECIMAL(21, 2) NULL DEFAULT "0.0" COMMENT '最近1天消耗金额',
) ENGINE=OLAP
UNIQUE KEY(`stat_date`, `media_platform`)
COMMENT '广告域-广告组粒度1天汇总表'
DISTRIBUTED BY HASH(`stat_date`,`media_platform`) BUCKETS 6
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

表中数据量5210行数据

查询sql

SELECT 
  daily,cost
FROM
  (SELECT 
    stat_date AS daily,
    SUM(spend_amt_1d) cost
  FROM
    database.`table_1d` 
  WHERE adv_id = 12377 
  GROUP BY stat_date 
  ORDER BY cost DESC ) tmp_count
  WHERE cost=0
 ORDER BY daily DESC

每次的结果都是不一致的

结果一:

daily |cost|
----------+----+
2024-05-05|0.00|
2024-05-03|0.00|
2024-04-26|0.00|
2024-04-22|0.00|
2024-04-21|0.00|
2024-04-19|0.00|
2024-04-15|0.00|
2024-04-14|0.00|
2024-04-12|0.00|

结果二:

daily |cost|
----------+----+
2024-05-09|0.00|
2024-05-05|0.00|
2024-05-03|0.00|
2024-05-01|0.00|
2024-04-26|0.00|
2024-04-22|0.00|
2024-04-21|0.00|
2024-04-19|0.00|
2024-04-16|0.00|
2024-04-15|0.00|
2024-04-14|0.00|
2024-04-13|0.00|
2024-04-12|0.00|

结果三:

daily |cost|
----------+----+
2024-05-05|0.00|
2024-05-03|0.00|
2024-04-26|0.00|
2024-04-22|0.00|
2024-04-21|0.00|
2024-04-19|0.00|
2024-04-12|0.00|

只有当SUM(spend_amt_1d) cost的值为0.0的情况下才出现这个问题

spend_amt_1d 字段下没有null值

1 Answers

可能是 ORDER BY cost DESC 每次排序的结果不一致导致的,可以尝试把这段注释掉试试