主页 / dba / 问题 / 63031
Accepted
DaveB
Asked: 2014-04-12 08:26:11 +0800 CST

PostgreSQL 扩展更新查询

  • 772

我有以下更新查询来填充存储用户统计信息的表

用户：

int (pk) user_id

上次访问日期

int (fk) local_site

网站：

整数 (pk) 站点 ID

网站统计：

int (fk) site_id

int total_users

int monthly_users

国际每周用户

要填充“total_users”列，我使用以下查询...

UPDATE site_stats
SET
    total_users = totalUsers,
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site

上面的查询工作正常，我现在想扩展它以更新 monthly_users 和 weekly_users

是否可以在同一个查询中执行？

我最初考虑使用窗口函数，但在阅读文档后我不太确定它是否可行，例如。

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(u.*) OVER ( ** where u.last_visit > now()::DATE - 30 ** ) AS monthlyUsers,
        count(u.*) OVER ( ** where u.last_visit > now()::DATE - 7 ** ) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site

或者子查询会更合适吗？

postgresql window-functions

1 个回答

  1. Best Answer

    你不需要窗口函数：

    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 ;

    请检查您是否需要>=或>符合条件。最好（在我看来）将日期和时间检查为">= start_date"and "< end_date"，换句话说，使用封闭开放的日期时间间隔。

    • 2

相关问题