sql - 将长行转换为宽行，填充所有单元格

Question

我有关于企业的长格式数据，每次移动到不同位置时都有一行，以企业 ID 为键——任何一个企业都可以有多个移动事件。

我希望 reshape 为宽格式，这通常是每个 tablefunc 模块的交叉表区域。

+-------------+-----------+---------+---------+
| business_id | year_move |  long   |   lat   |
+-------------+-----------+---------+---------+
|   001013580 |      1991 | 71.0557 | 42.3588 |
|   001015924 |      1993 | 71.0728 | 42.3504 |
|   001015924 |      1996 | -122.28 | 37.654  |
|   001020684 |      1992 | 84.3381 | 33.5775 |
+-------------+-----------+---------+---------+

然后我这样转换:

SELECT longbyyear.*
FROM crosstab($$
    SELECT 
    business_id, 
    year_move, 
    max(longitude::float)
    from business_moves
    where year_move::int between 1991 and 2010 
    group by business_id, year_move
    order by business_id, year_move;
    $$
) 
AS longbyyear(biz_id character varying, "long91" float,"long92" float,"long93" float,"long94" float,"long95" float,"long96" float,"long97" float, "long98" float, "long99" float,"long00" float,"long01" float,
"long02" float,"long03" float,"long04" float,"long05" float, 
"long06" float, "long07" float, "long08" float, "long09" float, "long10" float);

它——主要是——让我得到了想要的输出。

+---------+----------+----------+----------+--------+---+--------+--------+--------+
| biz_id  |  long91  |  long92  |  long93  | long94 | … | long08 | long09 | long10 |
+---------+----------+----------+----------+--------+---+--------+--------+--------+
| 1000223 | 121.3784 | 121.3063 | 121.3549 | 82.821 | … |        |        |        |
| 1000678 | 118.224  |          |          |        | … |        |        |        |
| 1002158 | 121.98   |          |          |        | … |        |        |        |
| 1004092 | 71.2384  |          |          |        | … |        |        |        |
| 1007801 | 118.0312 |          |          |        | … |        |        |        |
| 1007855 | 71.1769  |          |          |        | … |        |        |        |
| 1008697 | 71.0394  | 71.0358  |          |        | … |        |        |        |
| 1008986 | 71.1013  |          |          |        | … |        |        |        |
| 1009617 | 119.9965 |          |          |        | … |        |        |        |
+---------+----------+----------+----------+--------+---+--------+--------+--------+

唯一的障碍是我最好为每一年填充值，而不仅仅是移动年份的值。因此，所有字段都将被填充，每年都有一个值，最近的地址会延续到下一年。如果每个都是空白，我可以通过手动更新来解决这个问题，使用上一列，我只是想知道是否有一种聪明的方法可以使用 crosstab() 函数或其他方式来做到这一点，可能加上自定义函数。

Answer 1

为了获得任何给定年份的每个 business_id 的当前位置，您需要做两件事:

1. 用于选择年份的参数化查询，作为 SQL 语言函数实现。
2. 按年汇总、按 business_id 分组并保持坐标不变的肮脏把戏。这是通过 CTE 中的子查询完成的。

函数看起来像这样:

CREATE FUNCTION business_location_in_year_x (int) RETURNS SETOF business_moves AS $$
  WITH last_move AS (
    SELECT business_id, MAX(year_move) AS yr
    FROM business_moves
    WHERE year_move <= $1
    GROUP BY business_id)
  SELECT lm.business_id, $1::int AS yr, longitude, latitude
  FROM business_moves bm, last_move lm
  WHERE bm.business_id = lm.business_id
  AND bm.year_move = lm.yr;
$$ LANGUAGE sql;

子查询只为每个营业地点选择最近的搬迁。主查询然后添加经度和纬度列并将请求的年份放入返回表中，而不是最近移动发生的年份。一个警告:您需要在此表中有一条记录，给出每个 business_id 的建立和初始位置，否则它只有在移动到其他地方后才会显示。

使用通常的 SELECT * FROM business_location_in_year_x(1997) 调用此函数。另见 SQL fiddle .

如果您真的需要交叉表，那么您可以调整此代码，为您提供一系列年份的营业地点，然后将其输入 crosstab() 函数。