-- 不好的写法（无法利用分区裁剪）
SELECT * FROM partitioned_view 
WHERE DATE(timestamp_column) = '2023-01-01'

-- 好的写法（能利用分区裁剪）
SELECT * FROM partitioned_view 
WHERE timestamp_column BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'

-- 优化前（复杂视图）
CREATE OR REPLACE VIEW my_view AS
SELECT 
  a.*,
  b.extra_field,
  SUM(c.value) OVER (PARTITION BY a.id) as running_total
FROM partitioned_table a
JOIN lookup_table b ON a.id = b.id
LEFT JOIN historical_data c ON a.id = c.id

-- 优化后（简化视图）
CREATE OR REPLACE VIEW my_simplified_view AS
SELECT 
  a.*,
  b.extra_field
FROM partitioned_table a
JOIN lookup_table b ON a.id = b.id

-- 添加日期范围限制
SELECT * FROM partitioned_view
WHERE date_column BETWEEN '2023-01-01' AND '2023-01-07'
AND other_conditions = 'value'

-- 考虑调整分区粒度（创建表时）
CREATE TABLE my_partitioned_table
PARTITION BY DATE_TRUNC(date_column, MONTH)  -- 按月分区而非按日
AS SELECT * FROM source_data

CREATE OR REPLACE TABLE my_dataset.sales_partitioned
PARTITION BY DATE(transaction_time)
AS 
SELECT * FROM my_dataset.sales_source;

CREATE OR REPLACE VIEW my_dataset.sales_view
AS
SELECT 
  transaction_id,
  customer_id,
  product_id,
  amount,
  DATE(transaction_time) AS transaction_date,
  transaction_time
FROM my_dataset.sales_partitioned;

-- 高效查询（利用分区裁剪）
SELECT 
  product_id,
  SUM(amount) as total_sales
FROM my_dataset.sales_view
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

-- 删除旧分区
DELETE FROM my_dataset.sales_partitioned
WHERE DATE(transaction_time) < DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR);

-- 查询分区元数据
SELECT 
  partition_id,
  total_rows,
  total_bytes
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'sales_partitioned';