当筛选日期晚于最大可用日期时，如何获取各产品最新日期的列值？

核心逻辑 #

当选择的筛选日期晚于某产品的max_date_aval时，自动用该产品的最大可用日期作为计算基准；若筛选日期在可用范围内，仍用筛选日期计算。通过这个逻辑保证所有产品始终出现在结果中。

具体实现 #

• 确定每个产品的有效计算日期
  先关联table1和筛选参数，为每个产品算出实际要用的计算日期：

  WITH product_calc_dates AS (
      SELECT
          product_name,
          max_date_aval,
          -- 取筛选日期和产品最大可用日期的较小值
          LEAST('${selected_date}'::DATE, max_date_aval) AS effective_date
      FROM table1
  )
  

• 计算日销售额与月累计销售额
  基于有效日期关联table2，计算所需指标，用LEFT JOIN确保所有产品都被保留：

  SELECT
      pcd.product_name,
      -- 日销售额：匹配有效日期的销售额，无数据则填0
      COALESCE(daily.sales, 0) AS daily_sales,
      -- 月累计销售额：计算有效日期所在月的累计值
      COALESCE(mtd.total_sales, 0) AS MTD
  FROM product_calc_dates pcd
  -- 关联日销售额数据
  LEFT JOIN table2 daily
      ON pcd.product_name = daily.product_name
      AND pcd.effective_date = daily.rec_date
  -- 关联月累计销售额汇总数据
  LEFT JOIN (
      SELECT
          product_name,
          DATE_TRUNC('month', rec_date) AS sales_month,
          SUM(sales) AS total_sales
      FROM table2
      GROUP BY product_name, DATE_TRUNC('month', rec_date)
  ) mtd
      ON pcd.product_name = mtd.product_name
      AND DATE_TRUNC('month', pcd.effective_date) = mtd.sales_month
  ORDER BY pcd.product_name;
  

• 仪表盘适配
  将仪表盘日历筛选器的选中日期以参数${selected_date}的形式传入上述SQL即可。无论选中的日期是否超出某产品的最大可用日期，该产品都会展示其最大可用日期对应的日销售额和当月累计销售额。

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