设置.

BEGIN;
CREATE TABLE client (
    id bigint,
    name text
);
CREATE TABLE document (
    id bigint,
    name text,
    expiration_date date,
    client_id int
);
INSERT INTO client
SELECT
    i,
    'client_' || i
FROM
    generate_series(1, 5) _ (i);
INSERT INTO document (id, name)
SELECT
    i,
    'document' || i
FROM
    generate_series(1, 5) _ (i),
    generate_series(1, 3, 1);
COMMIT;

UPDATE
    document
SET
    client_id = (4 * random())::int + 1;

UPDATE
    document
SET
    expiration_date = (date '2022-10-25' + (random() * 30)::int);

查询

SELECT DISTINCT
    c.id,
    c.name,
    count(expiration_date) FILTER (WHERE expiration_date > CURRENT_DATE -30) OVER (PARTITION BY d.id) AS count_last_month,
    count(expiration_date) FILTER (WHERE expiration_date > CURRENT_DATE) OVER (PARTITION BY d.id) AS count_expire
FROM
    client c
    LEFT JOIN document d ON d.id = c.id
ORDER BY
    c.id;

或者简单的一个。

SELECT
    c.id,
    c.name,
    count(expiration_date) FILTER (WHERE expiration_date > CURRENT_DATE -30) AS count_last_month,
    count(expiration_date) FILTER (WHERE expiration_date > CURRENT_DATE) AS count_expire
FROM
    client c
    LEFT JOIN document d ON d.id = c.id
GROUP BY
    1,
    2
ORDER BY
    c.id;

我不知道ruby，但从this发布，我认为你可以运行纯sql查询。