这是我的表tab_test":
Here's my table 'tab_test':
year animal price
2000 kittens 79
2000 kittens 93
2000 kittens 100
2000 puppies 15
2000 puppies 32
2001 kittens 31
2001 kittens 17
2001 puppies 65
2001 puppies 48
2002 kittens 84
2002 kittens 86
2002 puppies 15
2002 puppies 95
2003 kittens 62
2003 kittens 24
2003 puppies 36
2003 puppies 41
2004 kittens 65
2004 kittens 85
2004 puppies 58
2004 puppies 95
2005 kittens 45
2005 kittens 25
2005 puppies 15
2005 puppies 35
2006 kittens 50
2006 kittens 80
2006 puppies 95
2006 puppies 49
2007 kittens 40
2007 kittens 19
2007 puppies 81
2007 puppies 38
2008 kittens 37
2008 kittens 51
2008 puppies 29
2008 puppies 72
2009 kittens 84
2009 kittens 26
2009 puppies 49
2009 puppies 34
2010 kittens 75
2010 kittens 96
2010 puppies 18
2010 puppies 26
2011 kittens 35
2011 kittens 21
2011 puppies 90
2011 puppies 18
2012 kittens 12
2012 kittens 23
2012 puppies 74
2012 puppies 79
这是一些转换行和列的代码,因此我得到了小猫"和小狗"的平均值:
Here's some code that transposes the rows and columns so I get an average for 'kittens' and 'puppies':
SELECT
year,
AVG(CASE WHEN animal = 'kittens' THEN price END) AS "kittens",
AVG(CASE WHEN animal = 'puppies' THEN price END) AS "puppies"
FROM tab_test
GROUP BY year
ORDER BY year;
上面代码的输出是:
year kittens puppies
2000 90.6666666666667 23.5
2001 24.0 56.5
2002 85.0 55.0
2003 43.0 38.5
2004 75.0 76.5
2005 35.0 25.0
2006 65.0 72.0
2007 29.5 59.5
2008 44.0 50.5
2009 55.0 41.5
2010 85.5 22.0
2011 28.0 54.0
2012 17.5 76.5
我想要的是一个像第二个一样的表,但它只包含第一个表中 COUNT()
至少为 3 的项目.换句话说,目标是将 this 作为输出:
What I'd like is a table like the second one, but it would only contain items which had a COUNT()
of at least 3 in the first table. In other words, the goal is to have this as output:
year kittens
2000 90.6666666666667
第一个表中至少有 3 个小猫"实例.这在 PostgreSQL 中可行吗?
There were at least 3 instances of 'kitten' in the first table. Is this possible in PostgreSQL?
这是 @bluefeet 的建议的替代方案,它有点相似但避免了连接(相反,上层分组应用于已经分组的结果集):
Here's an alternative to @bluefeet's suggestion, which is somewhat similar but avoids the join (instead, the upper level grouping is applied to the already grouped result set):
SELECT
year,
MAX(CASE animal WHEN 'kittens' THEN avg_price END) AS "kittens",
MAX(CASE animal WHEN 'puppies' THEN avg_price END) AS "puppies"
FROM (
SELECT
animal,
year,
COUNT(*) AS cnt,
AVG(Price) AS avg_price
FROM tab_test
GROUP BY
animal,
year
) s
WHERE cnt >= 3
GROUP BY
year
;