问题[aggregate](dba)

我有两个不同的查询，需要将它们合并在一起，然后将每个费率类型组聚合到最终表中。对于每个费率组（Rate_001mo、Rate_003mo 等）

SELECT EffectiveDate, 
SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR012M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR024M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
SUM(CASE WHEN RateName = 'FHLBCSOFR036M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
FROM dbo.STAGING_FhlbRates WHERE EffectiveDate = '20250131'
group by EffectiveDate

UNION

SELECT EffectiveDate,
    SUM(CASE WHEN RateName = 'USOSFRA' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
    SUM(CASE WHEN RateName = 'USOSFRC' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
    SUM(CASE WHEN RateName = 'USOSFRF' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
    SUM(CASE WHEN RateName = 'USOSFR1' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
    SUM(CASE WHEN RateName = 'USOSFR2' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
    SUM(CASE WHEN RateName = 'USOSFR3' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
    FROM dbo.STAGING_SofrOisRates WHERE EffectiveDate = '20250131'
    GROUP BY EffectiveDate

最终结果表的列应包括生效日期，然后是每个费率组（Rate_001mo、Rate_003mo 等）

我有两张这样的桌子；

发票

id | total_amount | currency | discount

付款

id | invoice | amount

正如您所猜测的，付款表保存对特定发票的付款记录。

我需要查询以获取已部分支付的发票；

SELECT
    invoices.*,
    SUM(payments.amount) as paid
FROM
    invoices
LEFT JOIN
    payments ON payments.invoice = invoices.id
GROUP BY
    invoices.id

我能够通过此查询获得付款金额，但我也需要做类似的事情

WHERE paid < total_amount AND paid > 0

但这表示“未知列在‘where 子句’中‘付费’”。我怎样才能得到这份工作？

在聚合一个数组时，我需要删除空字符串，然后组合所有相邻的相同值。例如：

["","product","product","","product","","product","product","","product","product","","product","","","collection","product","","","product","product","","collection","order","checkout",""]

应该变成：

["product","collection","product","collection","order","checkout"]

我有一个带有 4 个嵌套选择的工作查询：

SELECT array_agg( page_type_unique_pre) FILTER (WHERE page_type_unique_pre != '')
                                        OVER   (ORDER BY event_time) AS page_type_journey_unique

FROM  (
   SELECT CASE WHEN lag(last_page_type) OVER (ORDER BY event_time) LIKE '%' || page_type || '%' THEN ''
               ELSE page_type END AS page_type_unique_pre
        , page_type
        , event_time
   FROM  (
      SELECT string_agg(page_type, ',') OVER (ORDER BY event_time) AS page_type_journey
           , first_value(page_type) OVER (PARTITION BY last_page_type_partition ORDER BY event_time) AS last_page_type
           , page_type
           , event_time
      FROM  (
         SELECT
         sum(CASE WHEN page_type IS NULL OR page_type = ''  THEN 0 ELSE 1 END) OVER (ORDER BY event_time) AS last_page_type_partition,
         page_type,
         event_time
         FROM (
            SELECT * FROM tes
            ) a
         ) b
      ) c
   ) d;

请参阅此小提琴中的测试用例。

我确定有更好的方法来实现这一目标吗？

我有一个包含一些帐户的表格，以及他们订阅的开始和结束日期。但是，这些订阅有时会重叠，我需要每个连接订阅期的开始和结束日期。就像在示例图像中一样。

我尝试将订阅期与日期参考表合并，并在有订阅时标记日期。然而，代码变得相当复杂。我想一定有一个更简单的解决方案。

IF OBJECT_ID('tempdb..#Subscriptions') IS NOT NULL DROP TABLE #Subscriptions
CREATE TABLE #Subscriptions (
    account_id varchar(1)
    ,start_date date
    ,end_date date
)

INSERT INTO #Subscriptions (account_id, start_date, end_date) values
('A','2019-06-20','2019-06-29'),
('A','2019-06-25','2019-07-25'),
('A','2019-07-20','2019-08-26'),
('A','2019-12-25','2020-01-25'),
('A','2021-04-27','2021-07-27'),
('A','2021-06-25','2021-07-14'),
('A','2021-07-10','2021-08-14'),
('A','2021-09-10','2021-11-12'),
('B','2019-07-13','2020-07-14'),
('B','2019-06-25','2019-08-26')

我正在努力尝试将表中的结果聚合到嵌套的 json 中。

这是表格：

+----+------+--------+------+------+-------+-----------+--------------+
| id | area | userId | game | step | score | completed | validAnswers |
+----+------+--------+------+------+-------+-----------+--------------+
|  1 |    2 |     21 |    7 |   53 |    10 |         0 |            0 |
|  2 |    2 |     37 |    7 |   53 |     0 |         0 |            0 |
|  3 |    2 |     21 |    7 |   53 |    10 |         0 |            0 |
|  4 |    2 |     37 |    7 |   53 |    10 |         0 |            0 |
...
| 37 |    3 |     21 |    7 |   57 |    80 |         1 |            8 |
| 38 |    2 |     21 |    8 |   56 |    80 |         1 |            8 |
| 39 |    2 |     21 |    7 |   58 |   100 |         1 |           10 |
| 40 |    2 |     21 |    7 |   59 |    50 |         1 |            5 |
+----+------+--------+------+------+-------+-----------+--------------+

我想创建一个显示如下内容的视图：

+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| userId | completedSteps                                                                                                                                                      |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     21 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
|     18 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
|     23 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
|     11 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+

将所有游戏及其步骤分组到它们所属的区域中。 这是我创建的 dbfiddle。

我尝试了不同的方法，比如从

select userId,     
       json_arrayagg(
                select distinct area
                from scoreTable st2
                where st1.userId =st2.userId and
                st1.area=st2.area
                group by area
        ) as completedSteps
from scoreTable st1
where completed = 1
group by userId ;

奇怪的是，它没有按区域分组，或者

select userId,
       json_objectagg('areas',
                      (select distinct area
                       from scoreTable st2 
                       where st1.userId =st2.userId
                       group by area)) as completedSteps
from scoreTable st1
where completed = 1
group by userId ;

或许多其他尝试。我可以获得离散区域结果，例如：

select area,
       json_object('games',json_object('id',game,'steps',JSON_ARRAYAGG(step))) as completedSteps
from scoreTable
where completed = 1
group by userId,area,game;
                

但是任何试图将区域聚合成对象数组的尝试都失败了。任何人都可以帮助我了解我错过了什么吗？

更新：

这更接近我想要得到的东西：

select userId, json_arrayagg(json_object('completed',completed)) as completed
from (
select distinct userId, json_arrayagg(json_object('area',area,'games',completed)) as completed
from (
    select distinct userId,area, json_object('id',game,'steps',(json_arrayagg(step))) as completed
from scoreTable
where completed = 1  and userId = 21
group by area,game
) st1
group by userId, area
) st3
group by userId

但仍然没有将嵌套它们的游戏分组到区域超级对象中。继续挣扎。。

我有 2 个表，一个包含人员，另一个包含参考人员 ID 的注册。我必须创建一个视图，其中仅包含注册中出现超过 4 次的结果。

我想我需要使用count()，但我无法计算我需要它来做什么。你如何输出一个只包含在登记表中出现超过 4 次的人的表？

以下是一些示例行以及我尝试做的事情：

表enrolment：

学生 1010093 和 1010138 符合标准，因为他们出现了 4 次以上。但是有很多学生的参赛作品较少。

表people：

（id 是学生栏内注册所指的 id）。

我的尝试是这样的：

create or replace view Q1(uniid,name) as
select people.uniid, people.name
from people left outer join enrolments on (people.id = enrolments.student)
group by people.uniid, people.name having count(enrolments.student) > 4;

样本输出：

尝试使用输入几何按距离排序t(x)，同时与 JOINed 表中的几何进行比较。

WITH "nearest_groups" as (
    SELECT groups.id, ST_Distance(t.x, locations.center) AS nearest
    FROM (SELECT ST_GeographyFromText('SRID=4326;POINT(-121.0611 39.2191)')
    ) AS t(x), groups
    INNER JOIN locations ON groups.location_id = locations.id
    WHERE ST_DWithin(t.x, locations.center, 300000)
) 
SELECT *
FROM "groups"
INNER JOIN "nearest_groups" ON "groups"."id" = "nearest_groups"."id" 
ORDER BY "nearest_groups"."nearest" asc

错误：列“nearest_groups.nearest”必须出现在 GROUP BY 子句中或用于聚合函数*

我不明白错误度量需要我做什么才能使这个查询工作。在那里扔一个 GROUP BY 有意义吗？我也不熟悉聚合函数。

（！！！）查询似乎在 PSQL 中运行良好，但在我们的 ORM 环境（bookshelfjs/Knex）中却不行。我觉得这令人震惊；ORM 给了我永远存在的恐惧，我将不得不与他们搏斗，让他们做我想做的事

更新：我们正在使用 GraphQL，一个常见的模式是获取可以通过添加hasMore布尔值和total计数来分页的项目。所以在其他地方，正在编译“hasMore”和“total”，并且正在抛出这个错误，因为它们正在使用聚合函数

我有一个来自养鱼场的真实用例，其中养鱼场的增长取决于养鱼时养鱼场中鱼的平均大小。我已经将这个问题简化为我认为无法在 PostgreSQL 中表达的核心问题：一个聚合函数，其中的条件取决于该聚合的先前计算的值。

操作的数据是一系列事务。

create table transactions (
    id           bigserial primary key,
    feed_g       bigint  
);

insert into transactions
    (feed_g)
values
    (50),
    (50),
    (50),
    (50);

计算这些行的总和很简单。

select
    id,
    feed_g,
    sum(feed_g) over (order by id) as simple_sum
from transactions;

--  id | feed_g | simple_sum 
-- ----+--------+------------
--   1 |     50 |         50
--   2 |     50 |        100
--   3 |     50 |        150
--   4 |     50 |        200

使用取决于输入行值的条件计算总和也很简单。在下面的查询中，将始终使用第二种情况。

select
    id,
    feed_g,
    sum(
        case when feed_g > 75 then feed_g
             else                  feed_g * 0.5
        end
    ) over (order by id) as row_weighted_sum
from transactions;

--  id | feed_g | row_weighted_sum 
-- ----+--------+------------------
--   1 |     50 |             25.0
--   2 |     50 |             50.0
--   3 |     50 |             75.0
--   4 |     50 |            100.0

我不知道该怎么做是编写一个查询，其中聚合函数中的条件取决于前一行的相同聚合函数计算的输出。

下面是一些不工作的伪 SQL。

select
    id,
    feed_g,
    sum(
        case when lag(recursive_sum) + feed_g  > 75 then feed_g
             else                                        feed_g * 0.5
        end
    ) over (order by id) as recursive_sum
from transactions;

-- The imagined output would be the following:
--  id | feed_g | row_weighted_sum 
-- ----+--------+------------------
--   1 |     50 |             25.0
--   2 |     50 |             50.0
--   3 |     50 |            100.0
--   4 |     50 |            150.0

将simple_sum用作 的输入recursive_sum似乎不是一个可行的解决方案，因为它们会随着时间的推移而分道扬镳。在给定的小型示例数据集中，这种漂移会影响第二行，其中在simple_sum第 3 行之前它不应该发生在第 2 行的阈值交叉处。

with estimate as (
    select
        id,
        feed_g,
        sum(feed_g) over (order by id) as simple_sum
    from transactions
)
select
    id,
    feed_g,
    simple_sum,
    sum(
        case when simple_sum > 75 then feed_g
             else                      feed_g * 0.5
        end
    ) over (order by id) as simple_sum_weighted_sum
from estimate;

--  id | feed_g | simple_sum | simple_sum_weighted_sum 
-- ----+--------+------------+-------------------------
--   1 |     50 |         50 |                    25.0
--   2 |     50 |        100 |                    75.0
--   3 |     50 |        150 |                   125.0
--   4 |     50 |        200 |                   175.0

simple_sum_weighted_sum在调用中使用作为输入的第三步也lag不起作用，因为它“忘记”了除最后一行之外的所有内容的权重。

with estimate as (
    select
        id,
        feed_g,
        sum(feed_g) over (order by id) as simple_sum
    from transactions
),
est2 as (
select
    id,
    feed_g,
    simple_sum,
    sum(
        case when simple_sum > 75 then feed_g
             else                      feed_g * 0.5
        end
    ) over (order by id) as simple_sum_weighted_sum
from estimate)
select
    id,
    feed_g,
    simple_sum,
    simple_sum_weighted_sum,
    coalesce(lag(simple_sum_weighted_sum) over (order by id), 0)
        + case when simple_sum_weighted_sum > 75 then feed_g
               else                                   feed_g * 0.5
          end as row_weighted_sum
from est2;

--  id | feed_g | simple_sum | simple_sum_weighted_sum | row_weighted_sum 
-- ----+--------+------------+-------------------------+------------------
--   1 |     50 |         50 |                    25.0 |             25.0
--   2 |     50 |        100 |                    75.0 |             50.0
--   3 |     50 |        150 |                   125.0 |            125.0
--   4 |     50 |        200 |                   175.0 |            175.0

我在 Python 中编写了该算法的两个工作实现以供参考。这是第一个命令式风格。

data = (50, 50, 50, 50)
sum = 0
for value in data:
  if sum + value > 75:
    sum = sum + value
  else:
    sum = sum + value * 0.5
  print(value, sum)

# 50 25.0
# 50 50.0
# 50 100.0
# 50 150.0

这第二个功能风格有些发育不良。

data = (50, 50, 50, 50)

def data_dependant_recursive_sum(iterator, last_sum):
  try:
    value = next(iterator)
  except StopIteration:
    return
  recursively_weighted_value = value if last_sum + value > 75 else value * 0.5
  recursive_sum = recursively_weighted_value + last_sum
  print(value, recursive_sum)
  data_dependant_recursive_sum(iterator, recursive_sum)
  
data_dependant_recursive_sum(iter(data), 0)

# 50 25.0
# 50 50.0
# 50 100.0
# 50 150.0

如果这个练习感觉做作和荒谬，可以在这里找到这个问题的更复杂但完整的版本：https ://stackoverflow.com/questions/70158295

我目前正在使用 Postgres 12，但如果需要，升级到 14 会很容易。

这里是 SQL 初学者，请多多包涵。

假设我有一个songs看起来像这样的简化表

我也有一张桌子songartists：

假设我要查找 和 的歌曲K/DA，Seraphine这意味着没有只有两位艺术家中的一位的歌曲，也没有包含这两位艺术家以及其他艺术家的歌曲。

这是一些在技术上可以满足我要求的代码，但速度很慢：

SELECT title FROM songs WHERE
    EXISTS (SELECT * FROM songartists WHERE songartists.song_id == songs.id and songartists.artist == "K/DA")
    AND
    EXISTS (SELECT * FROM songartists WHERE songartists.song_id == songs.id and songartists.artist == "Seraphine")
    AND
    NOT EXISTS (SELECT * FROM songartists WHERE songartists.song_id == songs.id AND songartists.artist NOT IN ("K/DA","Seraphine"))

显然，我也可以对标题进行非常简单的查找，然后对艺术家进行比较并以编程方式进行比较。但是有没有一个高性能的 SQL 解决方案呢？

我使用 PostgreSQL。给定一组组，例如：

CREATE TABLE IF NOT EXISTS t (
   id INTEGER  NOT NULL,
   value TEXT NOT NULL
);

INSERT INTO t (id, value) VALUES
(1, 'A'),
(1, 'B'),
(2, 'B'),
(2, 'A'),
(3, 'A'),
(4, 'A'),
(4, 'B'),
(4, 'C');

我希望计算每组的行数并获得以下结果：

{'A', 'B'}: 2
{'A'}: 1
{'A', 'B', 'C'}: 1