从以日期为列的表中选择某些列

由盗墓贼发布于 2020-05-09 23:08:27

I have a table where column names are like years "2020-05","2020-06", "2020-07" etc and so many years as columns.I need to select only the current month, next month and third month columns alone from this table.(DB : PostgreSQL Version 11)

But since the column names are "TEXT" are in the format YYYY-MM , How can I select only the current month and future 2 months from this table without hard-coding the column names.

Below is the table structure , Name : static_data

所需的select语句是这样的，该表包含上面屏幕截图中的14个月数据，例如DATES列。从这开始，我想要当前月和接下来的2个月列以及它们的数据，如下所示。

从静态中选择“ 2020-05”，“ 2020-06”，“ 2020-07” -选择本月和接下来的2个月要求的输出：

布水风

2020-05-09 23:08:27

Technically, you can use the information schema to achieve this. But, like GMB said, please re-design your schema and do not approach this issue like this, in the first place.

The special schema information_schema contains meta-data about your DB. Among these is are details about existing columns. In other words, you can query it and convert their names into dates to compare them to what you need.
Here are a few hints.

查询现有列名。

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'your_schema'
  AND table_name   = 'your_table'

比较两个日期。

SELECT now() + INTERVAL '3 months' < now() AS compare;

 compare                                                       
---------
 f
(1 row)

您自己已经很接近转换了。

玩得开心，重新设计您的架构！

菊Oo

2020-05-09 23:08:27

免责声明：这无法回答您的问题-但是评论太久了。

您需要修复此表的设计。不要将日期存储在列中，而应将每个日期放在单独的行中。

当前设计有很多缺点：

非常简单的查询非常复杂：按日期过滤，汇总...所有这些操作都需要动态SQL，这增加了很多复杂性
添加或删除新日期需要修改表的结构
浪费存储空间的行不是所有列都已填充

Instead, consider this simple design, with one table that stores the master data of each item_sku, and a child table

create table myskus (
    item_sku int primary key,
    name text,
    cat_level_3_name text
);

create table myvalues (
    item_sku int references myskus(item_sku),
    date_sku date,
    value_sku text,
    primary key (item_sku, date_sku)
);

现在，您原来的问题很容易解决：

select v.*, s.name, s.cat_level_3_name
from myskus s
inner join myvalues v on v.item_sku = s.item_sku
where 
    v.date_sku >= date_trunc('month', now()) 
    and v.date_sku < date_trunc('month', now()) + interval '3 month'

盗墓贼

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3114
注册排名
1861