能否优化该WHERE子句？可否为目标SQL查询创建虚拟列？

一、WHERE子句优化方案 #

原语句的核心问题是：在created_datetime列上使用date()函数会导致索引失效（如果该列有索引的话），而且子查询会被重复执行多次。以下是几种优化思路：

1. 利用范围查询替代函数调用（保留索引可用性） #

如果created_datetime列有索引，改成范围查询可以让数据库直接利用索引过滤数据，避免全表扫描：

SELECT as1.col, as1.col2, as1.col3
FROM analytics.adjusted_sale_velocity as1
WHERE as1.created_datetime >= (
    SELECT MAX(DATE(created_datetime)) FROM analytics.adjusted_sale_velocity
)
AND as1.created_datetime < (
    SELECT MAX(DATE(created_datetime)) + INTERVAL 1 DAY FROM analytics.adjusted_sale_velocity
);

如果担心子查询重复执行，可以用CTE先缓存最大日期：

WITH max_date AS (
    SELECT MAX(DATE(created_datetime)) AS latest_date
    FROM analytics.adjusted_sale_velocity
)
SELECT as1.col, as1.col2, as1.col3
FROM analytics.adjusted_sale_velocity as1
CROSS JOIN max_date
WHERE as1.created_datetime >= max_date.latest_date
AND as1.created_datetime < max_date.latest_date + INTERVAL 1 DAY;

2. 使用窗口函数一次性获取最新日期数据 #

窗口函数可以在一次扫描中完成排序和过滤，适合数据量较大的场景：

SELECT col, col2, col3
FROM (
    SELECT 
        col, col2, col3,
        RANK() OVER (ORDER BY DATE(created_datetime) DESC) AS date_rank
    FROM analytics.adjusted_sale_velocity
) AS ranked_data
WHERE date_rank = 1;

如果同一天有多条数据，RANK()会保留所有同排名记录；如果只需要任意一条，用ROW_NUMBER()替代即可。

二、虚拟列的可行性 #

可以创建虚拟列，将DATE(created_datetime)的计算结果持久化或动态计算，具体取决于数据库类型：

1. MySQL（5.7+） #

创建存储型虚拟列（数据会实际存储，查询更快）：

ALTER TABLE analytics.adjusted_sale_velocity
ADD COLUMN date_created DATE AS (DATE(created_datetime)) STORED;

或者虚拟型（查询时动态计算，节省存储空间）：

ALTER TABLE analytics.adjusted_sale_velocity
ADD COLUMN date_created DATE AS (DATE(created_datetime)) VIRTUAL;

之后可以给虚拟列建索引，进一步优化查询：

CREATE INDEX idx_date_created ON analytics.adjusted_sale_velocity(date_created);

优化后的查询可以直接用虚拟列：

SELECT col, col2, col3
FROM analytics.adjusted_sale_velocity
WHERE date_created = (SELECT MAX(date_created) FROM analytics.adjusted_sale_velocity);

2. PostgreSQL（生成列） #

PostgreSQL支持生成列，语法类似：

ALTER TABLE analytics.adjusted_sale_velocity
ADD COLUMN date_created DATE GENERATED ALWAYS AS (DATE(created_datetime)) STORED;

同样可以给生成列创建索引，提升查询效率。

3. Oracle #

Oracle的虚拟列语法如下：

ALTER TABLE analytics.adjusted_sale_velocity
ADD (date_created DATE GENERATED ALWAYS AS (TRUNC(created_datetime)) VIRTUAL);

注意：虚拟列的可用性取决于你使用的数据库版本，确保你的数据库支持该特性。

内容的提问来源于stack exchange，提问作者user19935563