基于Hasura与PostgreSQL实现shipday唯一分组并支持多过滤条件

核心问题定位 #

当前view_shipday_and_filter产生重复shipday的原因是基于已分组的视图做过滤关联，而非从原始数据先过滤再分组，导致分组逻辑失效。以下是三种数据库端实现方案，均支持Hasura GraphQL直接查询：

方案一：带参数的SQL函数（推荐，支持动态组合过滤） #

创建接收过滤参数的函数，内部先过滤原始数据再按shipday分组，确保返回结果的shipday唯一：

CREATE OR REPLACE FUNCTION get_filtered_shipday(
    p_carrier TEXT DEFAULT NULL,
    p_start_shipdate DATE DEFAULT NULL,
    p_end_shipdate DATE DEFAULT NULL
) RETURNS TABLE(
    shipday TEXT, -- 替换为你的字段类型，比如INT表示星期几
    total_orders BIGINT,
    total_amount NUMERIC -- 替换为你需要的聚合字段
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        shipday,
        COUNT(*) AS total_orders,
        SUM(amount) AS total_amount
    FROM your_original_table
    WHERE 
        (p_carrier IS NULL OR carrier = p_carrier)
        AND (p_start_shipdate IS NULL OR shipdate >= p_start_shipdate)
        AND (p_end_shipdate IS NULL OR shipdate <= p_end_shipdate)
    GROUP BY shipday
    ORDER BY shipday;
END;
$$ LANGUAGE plpgsql STABLE;

操作步骤 #

1. 在PostgreSQL中执行上述函数
2. 在Hasura中将该函数追踪为查询
3. 客户端通过GraphQL传参过滤，示例查询：

   query GetFilteredShipday($carrier: String, $startDate: date, $endDate: date) {
     get_filtered_shipday(p_carrier: $carrier, p_start_shipdate: $startDate, p_end_shipdate: $endDate) {
       shipday
       total_orders
       total_amount
     }
   }
   

方案二：DISTINCT ON去重视图（适合无需聚合的场景） #

如果仅需保留唯一shipday，不需要聚合字段，可通过DISTINCT ON实现过滤后自动去重：

CREATE VIEW view_unique_shipday_filterable AS
SELECT DISTINCT ON(shipday)
    shipday,
    carrier,
    shipdate
FROM your_original_table
ORDER BY shipday, shipdate DESC; -- 按shipday分组，取每个组内最新的记录

操作步骤 #

1. 创建视图后，在Hasura中添加该视图的追踪
2. 客户端直接通过GraphQL的where条件过滤，Hasura会自动将过滤逻辑推送到底层SQL，返回的shipday天然唯一

方案三：直接基于原始表的过滤分组视图（适合固定过滤规则） #

如果过滤规则相对固定，可直接创建先过滤再分组的视图：

CREATE VIEW view_filtered_fixed_shipday AS
SELECT 
    shipday,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_amount
FROM your_original_table
WHERE 
    -- 这里写固定过滤规则，比如carrier IN ('UPS', 'FedEx')
    carrier IS NOT NULL
GROUP BY shipday
ORDER BY shipday;

注意事项 #

该方案仅适合过滤规则不常变动的场景，若需动态组合过滤，优先选择方案一。

内容的提问来源于stack exchange，提问作者mused