如何在Postgresql中显示特定状态下客户和产品的每种组合的最大数量？

本文介绍了如何在Postgresql中显示特定状态下客户和产品的每种组合的最大数量？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我刚刚开始学习Postgresql。 我有一个名为 sales的表：I just begin learning Postgresql recently.I have a table named 'sales':create table sales ( cust varchar(20), prod varchar(20), day integer, month integer, year integer, state char(2), quant integer )insert into sales values ('Bloom', 'Pepsi', 2, 12, 2001, 'NY', 4232);insert into sales values ('Knuth', 'Bread', 23, 5, 2005, 'PA', 4167);insert into sales values ('Emily', 'Pepsi', 22, 1, 2006, 'CT', 4404);insert into sales values ('Emily', 'Fruits', 11, 1, 2000, 'NJ', 4369);insert into sales values ('Helen', 'Milk', 7, 11, 2006, 'CT', 210);......它看起来像这样： 总共有500行。It looks like this:And there are 500 rows in total.现在，我想使用查询来实现这一点：Now I want to use the query to implement this: 对于客户和产品的每种组合，请分别在3个单独的列中输出 NY的最大销售量以及NJ和CT的最小销售量。与第一个报告类似，显示相应的日期（即，最大和最小销售数量的日期）。此外，对于CT和NJ，仅包括2000年以后发生的销售额；纽约的，包括所有销售额。 应该是这样的： It should be like this:我尝试了以下查询：SELECT cust customer, prod product, MAX(CASE WHEN rn3 = 1 THEN quant END) NY_MAX, MAX(CASE WHEN rn3 = 1 THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) date, MIN(CASE WHEN rn2 = 1 THEN quant END) NJ_MIN, MIN(CASE WHEN rn2 = 1 THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) date, MIN(CASE WHEN rn1 = 1 THEN quant END) CT_MIN, MIN(CASE WHEN rn1 = 1 THEN TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') END) dateFROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY cust, prod ORDER BY quant) rn1, ROW_NUMBER() OVER(PARTITION BY cust, prod ORDER BY quant) rn2, ROW_NUMBER() OVER(PARTITION BY cust, prod ORDER BY quant DESC) rn3 FROM sales ) xWHERE rn1 = 1 OR rn2 = 1 or rn3 = 1 GROUP BY cust, prod;结果是： This is the result:这是错误的，因为它显示了所有状态的最大数量和最小数量，而不是我想要的特定状态。而且我不知道该如何处理年份这一问题。This is wrong because it shows me the maximum number and minimum number of all states, not of the specific state I want. And I have no idea how to deal with the year as the question as me to do.推荐答案我们可以使用单独的方法进行处理CTE和日历表：We can handle this using separate CTEs along with a calendar table:WITH custprod AS ( SELECT DISTINCT cust, prod FROM sales),ny_sales AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cust, prod ORDER BY quant DESC) rn FROM sales WHERE state = 'NY'),nj_sales AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cust, prod ORDER BY quant) rn FROM sales WHERE state = 'NJ'),ct_sales AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cust, prod ORDER BY quant) rn FROM sales WHERE state = 'CT')SELECT cp.cust, cp.prod, nys.quant AS ny_max, nys.year::text || '-' || nys.month::text || '-' || nys.day::text AS ny_date, njs.quant AS nj_max, njs.year::text || '-' || njs.month::text || '-' || njs.day::text AS nj_date, cts.quant AS ct_max, cts.year::text || '-' || cts.month::text || '-' || cts.day::text AS ct_dateFROM custprod cpLEFT JOIN ny_sales nys ON cp.cust = nys.cust AND cp.prod = nys.prod AND nys.rn = 1LEFT JOIN nj_sales njs ON cp.cust = njs.cust AND cp.prod = njs.prod AND njs.rn = 1LEFT JOIN ct_sales cts ON cp.cust = cts.cust AND cp.prod = cts.prod AND cts.rn = 1ORDER BY cp.cust, cp.prod;注意：您没有提供全面的示例数据，但以上内容似乎在演示中起作用Note: You didn't provide comprehensive sample data, but the above seems to be working in the demo link below. 演示 这篇关于如何在Postgresql中显示特定状态下客户和产品的每种组合的最大数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！