主页 / dba / 问题 / 63198
Accepted
DaveB
Asked: 2014-04-15 14:35:32 +0800 CST

子查询结果的 Postgresql 条件

  • 772

这是一个基于我之前问题的解决方案的新问题

我有以下表格：

用户

int (pk) user_id
date last_visit
int (fk) local_site

网站

int (pk) site_id

网站统计

int (fk) site_id
int total_users
int monthly_users
int weekly_users

我正在使用这个查询来更新 user_stats 表

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers, 
        count(CASE WHEN u.last_visit >= now()::DATE - 7 THEN 1 END) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site ;

这工作正常，除了如果在时间范围内有 0 个用户链接到站点，则不会发生更新，因此表 col 将保留以前的值而不是显示 0。

添加条件以使任何具有 0 个链接用户的网站都更新为 0 的最佳方法是什么？

我的第一个猜测是CASE在WHERE子句中使用 a ？

postgresql update

1 个回答

  1. Best Answer

    要么您运行额外 UPDATE的（最好在一个事务中）：

    UPDATE site_stats s
SET    total_users   = 0
     , monthly_users = 0
     , weekly_users  = 0
WHERE  NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id)

    或者改用这个（我会这样做）：

    UPDATE site_stats s
SET    total_users   = COALESCE(u.total, 0)
     , monthly_users = COALESCE(u.monthly, 0)
     , weekly_users  = COALESCE(u.weekly, 0)
FROM   site_stats s0
LEFT   JOIN (
   SELECT local_site 
        , count(*) AS total
        , count(last_visit >= now()::date - 30 OR NULL) AS monthly
        , count(last_visit >= now()::date - 7  OR NULL) AS weekly
   FROM   users
   GROUP  BY 1
   ) u ON u.local_site = s0.site_id
WHERE  s.site_id = s0.site_id;

    包括其他一些小的简化。

    • 1

相关问题