如何用PL/pgSQL查询指定类的LCOM指标：无关联方法对

需求背景 #

基于给定的PostgreSQL四张表（class、method、attribute、attributeaccess），查询指定类中无公共访问属性的方法对，用于计算LCOM（Lack of Cohesion in Methods）指标。

表结构回顾 #

CREATE TABLE IF NOT EXISTS public.class
(
    id bigint NOT NULL DEFAULT nextval('class_id_seq'::regclass),
    name character varying(200) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT class_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.method
(
    id bigint NOT NULL DEFAULT nextval('method_id_seq'::regclass),
    name character varying(200) COLLATE pg_catalog."default",
    cid bigint,
    CONSTRAINT method_pkey PRIMARY KEY (id),
    CONSTRAINT method_cid_fkey FOREIGN KEY (cid)
        REFERENCES public.class (id) 
);

CREATE TABLE IF NOT EXISTS public.attribute
(
    id bigint NOT NULL DEFAULT nextval('attribute_id_seq'::regclass),
    name character varying(200) COLLATE pg_catalog."default" NOT NULL,
    cid bigint,
    CONSTRAINT attribute_pkey PRIMARY KEY (id),
    CONSTRAINT attribute_cid_fkey FOREIGN KEY (cid)
        REFERENCES public.class (id) 
);

CREATE TABLE IF NOT EXISTS public.attributeaccess
(
    id bigint NOT NULL DEFAULT nextval('attributeaccess_id_seq'::regclass),
    mid bigint,
    aid bigint,
    CONSTRAINT attributeaccess_aid_fkey FOREIGN KEY (aid)
        REFERENCES public.attribute (id),
    CONSTRAINT attributeaccess_mid_fkey FOREIGN KEY (mid)
        REFERENCES public.method (id) 
);

实现思路 #

1. 筛选目标类下的所有方法
2. 生成该类下所有不重复的方法对（避免(m1,m2)和(m2,m1)重复统计）
3. 找出所有存在公共访问属性的方法对
4. 从所有方法对中排除有公共属性的对，剩余即为目标方法对

纯SQL查询示例（指定class_id=1） #

WITH class_methods AS (
    SELECT id, name FROM method WHERE cid = 1
),
all_method_pairs AS (
    SELECT 
        m1.id AS method1_id, m1.name AS method1_name,
        m2.id AS method2_id, m2.name AS method2_name
    FROM class_methods m1
    JOIN class_methods m2 ON m1.id < m2.id
),
pairs_with_shared_attr AS (
    SELECT DISTINCT
        aa1.mid AS method1_id, aa2.mid AS method2_id
    FROM attributeaccess aa1
    JOIN attributeaccess aa2 ON aa1.aid = aa2.aid AND aa1.mid < aa2.mid
    WHERE aa1.mid IN (SELECT id FROM class_methods)
      AND aa2.mid IN (SELECT id FROM class_methods)
)
SELECT 
    amp.method1_id, amp.method1_name,
    amp.method2_id, amp.method2_name
FROM all_method_pairs amp
LEFT JOIN pairs_with_shared_attr psa 
    ON amp.method1_id = psa.method1_id AND amp.method2_id = psa.method2_id
WHERE psa.method1_id IS NULL;

PL/pgSQL函数封装（支持动态传入类ID） #

将查询逻辑封装为函数，方便复用：

CREATE OR REPLACE FUNCTION get_lcom_method_pairs(p_class_id bigint)
RETURNS TABLE (
    method1_id bigint,
    method1_name character varying(200),
    method2_id bigint,
    method2_name character varying(200)
) AS $$
BEGIN
    RETURN QUERY
    WITH class_methods AS (
        SELECT id, name FROM method WHERE cid = p_class_id
    ),
    all_method_pairs AS (
        SELECT 
            m1.id AS method1_id, m1.name AS method1_name,
            m2.id AS method2_id, m2.name AS method2_name
        FROM class_methods m1
        JOIN class_methods m2 ON m1.id < m2.id
    ),
    pairs_with_shared_attr AS (
        SELECT DISTINCT
            aa1.mid AS method1_id, aa2.mid AS method2_id
        FROM attributeaccess aa1
        JOIN attributeaccess aa2 ON aa1.aid = aa2.aid AND aa1.mid < aa2.mid
        WHERE aa1.mid IN (SELECT id FROM class_methods)
          AND aa2.mid IN (SELECT id FROM class_methods)
    )
    SELECT 
        amp.method1_id, amp.method1_name,
        amp.method2_id, amp.method2_name
    FROM all_method_pairs amp
    LEFT JOIN pairs_with_shared_attr psa 
        ON amp.method1_id = psa.method1_id AND amp.method2_id = psa.method2_id
    WHERE psa.method1_id IS NULL;
END;
$$ LANGUAGE plpgsql STABLE;

使用方法 #

调用函数传入目标类ID即可获取结果：

SELECT * FROM get_lcom_method_pairs(1);

返回结果的行数即为该类的LCOM值（如示例中返回2行，对应LCOM=2）。

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