使用來自另一個子查詢的WHERE對兩個子查詢總和進行Postgres劃分

我們將每個用戶的志愿者時間存盤在資料庫中，我試圖弄清楚如何構建 Postgres SQL 查詢以查找特定用戶在某個位置花費的總小時數與該位置的總小時數的百分比（任何用戶）

CREATE TABLE entries (
  id INT,
  userId INT,
  volunteerHours DOUBLE PRECISION NOT NULL,
  location INT
);

INSERT INTO entries (id, userId, volunteerHours, location) VALUES (1, 1, 3.0, 2);
INSERT INTO entries (id, userId, volunteerHours, location) VALUES (2, 1, 3.0, 1);
INSERT INTO entries (id, userId, volunteerHours, location) VALUES (3, 1, 3.0, 1);
INSERT INTO entries (id, userId, volunteerHours, location) VALUES (4, 2, 3.0, 1);
INSERT INTO entries (id, userId, volunteerHours, location) VALUES (5, 2, 3.0, 1);

資料庫中可能有數百個這樣的條目，這些條目僅作為示例顯示

偽代碼：

SELECT
(SELECT location from entries ORDER BY id DESC FETCH NEXT 1 ROWS ONLY) as lastEntryLocation,
(SELECT SUM(volunteerHours) from entries WHERE userId = 2 AND location = lastEntryLocation) as userHours,
(SELECT SUM(volunteerHours) from entries AND location = lastEntryLocation) as totalHours,
(SELECT userHours / totalHours) as userContributionPercentage 

我仍在學習如何撰寫自定義 SQL 查詢，希望有人至少可以指出我應該如何正確構建此查詢的正確方向，以在這種情況下回傳0.50

sudo 查詢的思考程序：

1. 拉取資料庫中的最新條目以確定最后插入的位置 ID 值
2. 然后使用最后位置值 ID 查詢SUM所有userId 2時間
3. 使用 last location value ID 查詢SUM所有用戶小時數
4. userId 2將 的總小時數除以該特定位置的總小時數

如果可能的話，我想嘗試在 SQL 查詢中完成所有這些操作（我可以將其分解為單獨的 SQL 查詢呼叫并以編程方式進行數學運算，但我更愿意通過一個 SQL 查詢呼叫來學習如何執行此操作，如果在一切皆有可能。

為此也創建了一個資料庫小提琴： https ://www.db-fiddle.com/f/7dfFZTa2yN3jpSMVLcFXWc/4

如果您還可以解釋為什么選擇用于為此創建查詢的特定方法，那將極大地幫助我理解其背后的原因，并知道在哪里可以進一步了解自己。謝謝！

編輯：尋找一種不必對最新位置進行兩次查詢的方法（因為這樣做感覺是錯誤的方法）

uj5u.com熱心網友回復：

具有每行磁區/組總和的視窗函式：

select distinct on (e.userId) 
    sum(e.volunteerHours) over (partition by userId,location,id)
      / sum(e.volunteerHours) over (partition by userId,location) 
            as percentage_volunteered_here
from entries e
where userId=1
order by userId, id desc;

Distinct on (a) a,c...order by a,b讓您a根據所需的順序/優先級為每個 獲取第一行 - 在您的情況下，最新位置。

使用子查詢的等價物：

select distinct on (userId) 
  (  select sum(volunteerHours) 
     from entries e2 
     where e1.userId=e2.userId 
     and e1.location=e2.location
     and e1.id=e2.id) --hours_during_this_visit_at_location
  /( select sum(volunteerHours) --total_hours_at_this_location
     from entries e2 
     where e1.userId=e2.userId
     and e1.location=e2.location) as percentage_volunteered_here
from entries e1
where userId=1
order by userId, id desc;

您可能會喜歡如何將您的思路一步一步地直接映射到一些WITH常見的表運算式中：

with 
 last_location_per_user as (
    select distinct on (userId) 
            userId, 
            location,
            volunteerHours
    from entries
    where userId=1
    order by userId, id desc
 ),
total_hours_at_last_location_per_user as (
    select  e.userId, 
            sum(e.volunteerHours)
    from    entries e
        inner join last_location_per_user l
            using (userId,location)
    group by userId
)
select  l.volunteerHours/t.sum as percentage_volunteered_here
from    last_location_per_user l,
        total_hours_at_last_location_per_user t
where   t.userId=l.userId;

演示

uj5u.com熱心網友回復：

這是您可以撰寫查詢的方式，但這不是唯一（最佳）方式

SELECT
  ( SELECT
      ( SELECT  SUM(volunteerHours) 
        from    entries 
        WHERE   userId = 2 
        AND     location = (
            SELECT  location 
            from    entries 
            ORDER BY id 
            DESC FETCH NEXT 1 ROWS ONLY))
        / 
      ( SELECT  SUM(volunteerHours) 
        from    entries 
        Where   location = (
            SELECT  location 
            from    entries 
            ORDER BY id DESC 
            FETCH NEXT 1 ROWS ONLY))
    ) as userContributionPercentage

標籤：数据库数据库postgresql

上一篇：SQLALCHEMY-回傳一個漂亮的格式

下一篇：在微服務架構中設計關系