时间戳

首页 > 解决方案 > 在视图 sql 中过滤嵌套表

sql - 在视图 sql 中过滤嵌套表

问题描述

我正在尝试基于此查询创建视图

with fields as (
    select id, data_id, value
    from test
    where data_id in (123, 345)
),
counted as (
    SELECT id, data_id, sum(meta.count) as count
    FROM fields
    LEFT JOIN meta ON meta.field_id = fields.id 
    group by data_id, meta.field_id, value
)

SELECT id, data_id, count
FROM (
    SELECT id, data_id, count,
    RANK() OVER (PARTITION BY data_id ORDER BY count DESC) dest_rank
    FROM counted
) t
WHERE dest_rank = 1

但是where data_id in (123, 345)需要自定义，以便我可以编写SELECT * from my_view where data_id in (123, 345)或使用 JOIN，例如

SELECT * FROM another_table LEFT JOIN my_view ON my_view.data_id = another_table.data_id

实现这一目标的最佳方法是什么？

标签： sqlpostgresql

解决方案

您可以使用自定义值作为参数创建一个函数：

CREATE OR REPLACE FUNCTION my_function (a int, b int)
RETURNS TABLE(id int, data_id int, count int) AS $$
    with fields as (
        select id, data_id, value
        from test
        where data_id in ($1, $2)
    ),
    ... 
$$ LANGUAGE SQL;

然后你可以做这个查询：

SELECT my_function(123, 456);

推荐阅读