postgresql 根据列中的值从两个不同的表中选择结果的SQL查询？

我必须学习PostgreSQL来完成我的工作，我在我的个人电脑上问这个问题，所以我会提供概念性的场景，而不是直接复制/粘贴代码，如果可以的话。这个任务正在Lambda中执行，超时15分钟。
我基本上需要这样的查询：
1.对于表firstTable ft中的记录#1，如果列firstTable ft中的起点和终点= secondTable st中的起点和终点
1.它搜索A-Z列，如果secondTable st* 中的A列、B列、C列一直到Z * 列中的任何值大于0，则特别获取这些特定列
1.但是我只想获取数据值大于0的前3列，如果没有，就返回0
1.然后，我将对所获取的3列的值求和
我能够以一种占用内存的方式完成此操作，方法是存储数据并在两个表中循环查找满足条件的记录，但我觉得SQL查询会更有效，但我没有任何SQL经验。
我保证这个场景是因为我觉得它会比解释实际的表/值/等更好，这不是出于懒惰，哈哈。
select st.column A，st.column B，etc. st.column Z FROM firstTable ft，secondTable st WHERE（基本上每个单独的列都大于0，但是不知道如何正确地写出来）限制3

1. secondTable，具有2+27列，分别命名为origin、destination，然后命名为A至Z

这里的问题是如何高效地扫描和分析这27列的值，一个解决方案是使用to_json函数将这27列转换为一个json对象：

SELECT to_json(st.*) - 'origin' - 'destination' AS object
  FROM firstTable ft
 INNER JOIN secondTable st
    ON ft.origin = st.origin 
   AND ft.destination = st.destination
 LIMIT 1 ;

然后根据您的条件过滤结果，使用json_each函数分解json对象，然后按升序排列json键，并选择前3个值〉0：

SELECT l.origin, l.destination, s.result
  FROM 
     ( SELECT st.origin
            , st.destination
            , to_json(st.*) - 'origin' - 'destination' AS object
         FROM firstTable ft
        INNER JOIN secondTable st
           ON ft.origin = st.origin 
          AND ft.destination = st.destination
        LIMIT 1
    ) AS l
CROSS JOIN LATERAL 
    ( SELECT COALESCE(sum(v.value :: integer), 0) AS result
        FROM json_each(l.object) AS v(key, value)
       WHERE v.value :: integer > 0
       ORDER BY v.key ASC
       LIMIT 3
    ) AS r

2. secondTable，具有2+1个名为origin、destination和"a-z"的列，类型为jsonb

如果大多数情况下只满足某些列，则只使用jsonb类型的一个名为"a-z"的列而不是27个列可能是相关的。列"a-z"包含一个jsonb对象，该对象的键对应于具有值的列A-Z。查询可以是：

SELECT st.origin, st.destination, r.result
  FROM firstTable ft
 INNER JOIN secondTable st
    ON ft.origin = st.origin 
   AND ft.destination = st.destination
CROSS JOIN LATERAL 
    ( SELECT COALESCE(sum(v.value :: integer), 0) AS result
        FROM json_each(st."a-z") AS v(key, value)
       WHERE v.value :: integer > 0
       ORDER BY v.key ASC
       LIMIT 3
    ) AS r
LIMIT 1

3. secondTable，包含2+2列，分别命名为origin、destination、label和value

在此数据库设计中，相同的（来源、目的地）最多可以有27行，其中值A到Z在label列中，对应的值在value列中。查询可以是：

SELECT l.origin, l.destination, sum(v.value) AS result
  FROM
     ( SELECT st.origin
            , st.destination
            , COALESCE((array_agg(st.value ORDER BY st.label ASC) FILTER (WHERE st.value > 0))[1:3], array[0]) AS arr
         FROM firstTable ft
        INNER JOIN secondTable st
           ON ft.origin = st.origin 
          AND ft.destination = st.destination
        GROUP BY st.origin, st.destination
        LIMIT 1
     ) AS l
 CROSS JOIN LATERAL unnest(l.arr) AS v(value)
 GROUP BY l.origin, l.destination

这些解决方案显然必须经过测试。

postgresql 根据列中的值从两个不同的表中选择结果的SQL查询？

1条答案

相关问题

热门标签

最新问答