视图中的动态列名称（Postgres） [英] Dynamic column names in view (Postgres)

问题描述

我当前正在编程一个SQL视图，该视图应提供特定月份的填充字段计数。

I am currently programming an SQL view which should provide a count of a populated field for a particular month.

这就是我希望构造视图的方式：

This is how I would like the view to be constructed:

Country   | (Current Month - 12) Eg Feb 2011 | (Current Month - 11) | (Current Month - 10)
----------|----------------------------------|----------------------|---------------------
UK        | 10                               | 11                   | 23

月份下的数字应为特定国家/地区所有填充字段的计数。该字段名为eldate，是格式为10-12-2011的日期（以char形式显示）。我希望计数只计算与月份匹配的日期。

The number under the month should be a count of all populated fields for a particular country. The field is named eldate and is a date (cast as a char) of format 10-12-2011. I want the count to only count dates which match the month.

因此，当前月-12列应仅包含属于现在的12个月前一个月内的日期计数。例如，本月-英国的12月应包括2011年2月以内的日期计数。

So column "Current Month - 12" should only include a count of dates which fall within the month which is 12 months before now. Eg Current Month - 12 for UK should include a count of dates which fall within February-2011.

我希望列标题能够实际反映出该月份因此：

I would like the column headings to actually reflect the month it is looking at so:

Country | Feb 2011 | March 2011 | April 2011
--------|----------|------------|------------
UK      | 4        | 12         | 0

是这样的：

SELECT c.country_name,
        (SELECT COUNT("C1".eldate) FROM "C1" WHERE "C1".eldate LIKE %NOW()-12 Months% AS NOW() - 12 Months
        (SELECT COUNT("C1".eldate) FROM "C1" WHERE "C1".eldate LIKE %NOW()-11 Months% AS NOW() - 11 Months
FROM country AS c
INNER JOIN "site" AS s using (country_id)
INNER JOIN "subject_C1" AS "C1" ON "s"."site_id" = "C1"."site_id"

显然，这行不通，只是让您了解我的意思。

Obviously this doesn't work but just to give you an idea of what I am getting at.

有什么想法吗？

感谢您的帮助，如有其他疑问，请询问。

Thank you for your help, any more queries please ask.

推荐答案

我的第一个倾向是生成此表：

My first inclination is to produce this table:

+---------+-------+--------+
| Country | Month | Amount |
+---------+-------+--------+
| UK      | Jan   | 4      |
+---------+-------+--------+
| UK      | Feb   | 12     |
+---------+-------+--------+

等。并旋转它。因此，您将从（例如）开始：

etc. and pivot it. So you'd start with (for example):

SELECT 
  c.country, 
  EXTRACT(MONTH FROM s.eldate) AS month, 
  COUNT(*) AS amount
FROM country AS c
JOIN site AS s ON s.country_id = c.id
WHERE 
  s.eldate > NOW() - INTERVAL '1 year'
GROUP BY c.country, EXTRACT(MONTH FROM s.eldate);

然后可以将其插入来自 tablefunc 模块的交叉表函数之一做到这一点，就像这样：

You could then plug that into one the crosstab functions from the tablefunc module to achieve the pivot, doing something like this:

SELECT * 
FROM crosstab('<query from above goes here>') 
  AS ct(country varchar, january integer, february integer, ... december integer);

这篇关于视图中的动态列名称（Postgres）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！