使用字符varying[]字段进行PostgreSQL和连接

Question

我继承了一个PostgreSQL 9.2.4数据库，虽然我在SQL Server方面有相当广泛的背景，但我在理解我遇到的一个问题时遇到了一点小麻烦。

我有一个表，其中有三个字段(以及其他字段)。"age_years“、"age_months”和"age_days“。如果表中的某人是2个月大或更小的，那么他们在"age_days“字段中有一个天数的值。如果它们不到3岁但大于2个月，则它们在"age_months“字段中有一个值。任何大于3的值，并且它们在"age_years“字段中有一个值。

给定记录在这三个字段中的一个字段中只有一个非零值。例如，永远不会出现age_days和age_years都有非零值的情况。这些记录代表就诊次数，年龄是就诊时个人的年龄。

在另一个表中，我有几个字符varying[]字段，最多可包含20个值。它们是ref_age_cd、ref_age、ref_clow和ref_chigh。以下是表中一个示例记录(其值少于最大值，仅用于显示目的)：

我为下面丑陋的台词道歉。我似乎不能让它们在一个非常可读的条件下格式化。

ref_age_cd | ref_age | ref_clow | ref_chigh

[D,D,D,M,M,Y,Y,Y]   [1,4,15,2,7,13,18,199]  [9.1,9.8,5.4,5.5,7.9,5.1,4.8,4.8]   [27.1,27.8,16.4,15.8,15.9,11.1,10.8,10.8]

ref_age_cd字段确定您正在查看的时间类型(天、月或年)。ref_age确定该值，然后根据这两个值从ref_clow和ref_chigh字段获得最低和最高值。例如，如果某人的age_months字段中有一个13，那么您将查看ref_age_cd并找到数组中的'M‘值，然后查看相应的ref_age字段并找到小于age_months字段中的值的最大值。因此，数组索引应该是5。然后，获取ref_clow和ref_chigh字段中的第五个值，用于表示低值和高值。(分别为7.9和15.9 )

如果一个人出生10天，那么要查看的数组索引将是2( 'D‘的ref_age_cd和ref_age为4)。这将表示9.8和27.8的低值和高值。如果他们是80岁，指数将是7 (ref_age_cd为'Y‘，ref_age为18)。4.8和10.8的低值和高值。

我只是不知道如何编程，所以当我从表A(带有age_days、age_months或age_years字段)连接到引用表时，我可以为ref_clow和ref_chigh提取正确的数组索引。

我还应该提到，我没有能力对此数据库进行任何更改。我需要用我所得到的来做这件事。

Answer 1

对于单个患者，尝试如下所示：

/* Creating test environment
CREATE TABLE refs (
  id serial NOT NULL,
  ref_age_cd character(1)[],
  ref_age integer[],
  ref_clow double precision[],
  ref_chigh double precision[],
  CONSTRAINT refs_pkey PRIMARY KEY (id)
);
INSERT INTO refs(ref_age_cd, ref_age, ref_clow, ref_chigh)
       VALUES ('{"D","D","D","M","M","Y","Y","Y"}',
               '{1,4,15,2,7,13,18,199}',
               '{9.1,9.8,5.4,5.5,7.9,5.1,4.8,4.8}',
               '{27.1,27.8,16.4,15.8,15.9,11.1,10.8,10.8}');
CREATE TABLE pats (
  id serial NOT NULL,
  name varchar(255) NOT NULL,
  age_years integer,
  age_months integer,
  age_days integer,
  CONSTRAINT pats_pkey PRIMARY KEY (id)
);
INSERT INTO pats
       VALUES (DEFAULT, 'newborn', NULL, NULL, 10),
              (DEFAULT, 'baby', NULL, 13, NULL),
              (DEFAULT, 'adult', 80, NULL, NULL);
*/

-- Replace filters here to select only one row...
WITH tt AS ( SELECT * FROM refs WHERE id = 1 )
SELECT w.*, ref_clow, ref_chigh
FROM ( SELECT row_number() OVER () AS nr, unnest AS ref_age_cd
       FROM UNNEST( (SELECT ref_age_cd FROM tt ) ), tt ) q1
JOIN ( SELECT row_number() OVER () AS nr, unnest AS ref_age
       FROM UNNEST( (SELECT ref_age FROM tt ) ), tt ) q2 USING ( nr )
JOIN ( SELECT row_number() OVER () AS nr, unnest AS ref_clow
       FROM UNNEST( (SELECT ref_clow FROM tt ) ), tt ) q3 USING ( nr )
JOIN ( SELECT row_number() OVER () AS nr, unnest AS ref_chigh
       FROM UNNEST( (SELECT ref_chigh FROM tt ) ), tt ) q4 USING ( nr )
JOIN ( SELECT id, name, age_years, age_months, age_days,
              CASE WHEN age_years IS NOT NULL THEN 'Y'
                   WHEN age_months IS NOT NULL THEN 'M'
                   WHEN age_days IS NOT NULL THEN 'D' END AS ref_age_cd,
              CASE WHEN age_years IS NOT NULL THEN age_years
                   WHEN age_months IS NOT NULL THEN age_months
                   WHEN age_days IS NOT NULL THEN age_days END AS age
       -- Replace filters here to select only one row...
       FROM pats WHERE id = 2
     ) w USING (ref_age_cd)
WHERE ref_age <= age
ORDER BY ref_age DESC
LIMIT 1;

输出：

2;"baby";<NULL>;13;<NULL>;"M";13;7.9;15.9

Answer 2

这最终起到了关键作用。张贴，以便其他人可以使用它。

--test data in first two "with" statements
with a AS (
  select 1 AS patient_nr, CAST(2 AS INT) AS age_days, CAST(NULL AS INT) AS age_months, CAST(NULL AS INT) AS age_years
  UNION ALL
  select  2 AS patient_nr, CAST(16 AS INT) AS age_days, CAST(NULL AS INT) AS age_months, CAST(NULL AS INT) AS age_years
  UNION ALL
  select  3 AS patient_nr, CAST(NULL AS INT) AS age_days, CAST(13 AS INT) AS age_months, CAST(NULL AS INT) AS age_years
  UNION ALL
  select  4 AS patient_nr, CAST(10 AS INT) AS age_days, CAST(NULL AS INT) AS age_months, CAST(NULL AS INT) AS age_years
  UNION ALL
  select  5 AS patient_nr, CAST(NULL AS INT) AS age_days, CAST(NULL AS INT) AS age_months, CAST(80 AS INT) AS age_years
), b as (
  SELECT ARRAY['D','D','D','M','M','Y','Y','Y'] AS ref_age_cd
       , ARRAY[1,4,15,2,7,13,18,199] AS ref_age
       , ARRAY[9.1,9.8,5.4,5.5,7.9,5.1,4.8,4.8] AS ref_clow
       , ARRAY[27.1,27.8,16.4,15.8,15.9,11.1,10.8,10.8] AS ref_chigh
), refTable AS (
SELECT unnest(ref_age_cd) ref_age_cd
 , unnest(ref_age) ref_age
 , unnest(ref_clow) ref_clow
 , unnest(ref_chigh) ref_chigh
  FROM b
), res AS (
SELECT A.*, rt.*, ROW_NUMBER() OVER(PARTITION BY patient_nr ORDER BY ref_age DESC) AS rn
  FROM A
  LEFT JOIN refTable rt ON (rt.ref_age_cd = 'D' AND a.age_days > rt.ref_age)
                        OR (rt.ref_age_cd = 'M' AND a.age_months > rt.ref_age)
                        OR (rt.ref_age_cd = 'Y' AND a.age_years > rt.ref_age)
 )
 SELECT * 
   FROM res
  WHERE rn = 1