你可以使用窗口函数如row_number（）来选择销售最高的月份。

WITH sales_year_and_month AS 
(
  SELECT t.*,
         ROW_NUMBER() OVER(PARTITION BY dd.year ORDER BY t.total_sales DESC) AS RN
  FROM (
         SELECT dd.year, 
                dd.month, 
                SUM(O.product_quantity * P.product_price_in_£) AS total_sales 
         from public.dim_date_times dd
         JOIN public.orders_table O
           ON O.date_uuid = dd.date_uuid
         JOIN public.dim_products P
           ON P.product_code = O.product_code
         GROUP BY dd.year, 
                  dd.month) t
)
SELECT year,
       month,
       total_sales
FROM sales_year_and_month
WHERE RN = 1

字符串

我相信你是在寻找每年最高销售额的年份，月份和total_sales的列表。使用row_number() over()可以方便地做到这一点。对于每个partition，数字从1开始，order by DESC确定哪一行被分配1（最高销售额）。然后我们只过滤所有等于1的行号。

WITH sales_year_and_month
AS (
    SELECT
          *
        , row_number() OVER (PARTITION BY year ORDER BY total_sales DESC) AS rn
    FROM (
        SELECT
              year
            , month
            , SUM(orders_table.product_quantity * dim_products.product_price_in_ £) AS total_sales
        FROM PUBLIC.dim_date_times
        JOIN PUBLIC.orders_table ON PUBLIC.orders_table.date_uuid = PUBLIC.dim_date_times.date_uuid
        JOIN PUBLIC.dim_products ON PUBLIC.dim_products.product_code = PUBLIC.orders_table.product_code
        GROUP BY year
            , month
        )
    )
SELECT
      year
    , month
    , total_sales
FROM sales_year_and_month
WHERE rn = 1
ORDER BY year

字符串