主页 / user-123371

Shinigami's questions

Martin Hope
Shinigami
Asked: 2018-08-07 07:23:20 +0800 CST
  • 2

这是一个后续问题：
Make custom aggregate function easier to use (accept more input types without creating variants)

给出此 SQL 语句：

SELECT
    c.name AS commodity_name,
    c.category AS commodity_category,
    l.name AS location_name,
    min(b.price)::numeric(8, 3) AS min_price,
    valued_min(b.price, g.name) AS gameversion_of_min_price,
    max(b.price)::numeric(8, 3) AS max_price,
    valued_max(b.price, g.name) AS gameversion_of_max_price,
    avg(b.price)::numeric(8, 3) AS avg_price,
    count(b.price) AS price_measure_count
FROM buy AS b
JOIN location AS l ON l.id = b.location_id
JOIN commodity AS c ON c.id = b.commodity_id
JOIN gameversion AS g ON g.id = b.gameversion_id
GROUP BY l.name, c.name, c.category
ORDER BY c.name, l.name;

上一个问题中的自定义聚合函数使用 ( value_min(), value_max()) 加上avg()和count()。

可以更有效地编写吗？也许有窗口功能？

期望的结果

前 18 行的示例

表定义：

CREATE TABLE public.commodity
(
    id bigint NOT NULL DEFAULT nextval('commodity_id_seq'::regclass),
    name character varying COLLATE pg_catalog."default" NOT NULL,
    category character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT commodity_pkey PRIMARY KEY (id),
    CONSTRAINT commodity_name_key UNIQUE (name)
);
CREATE TABLE public.location
(
    id bigint NOT NULL DEFAULT nextval('location_id_seq'::regclass),
    name character varying COLLATE pg_catalog."default" NOT NULL,
    parent_location_id bigint,
    type character varying COLLATE pg_catalog."default",
    can_trade boolean,
    CONSTRAINT location_pkey PRIMARY KEY (id),
    CONSTRAINT location_name_key UNIQUE (name),
    CONSTRAINT location_parent_location_id_fkey FOREIGN KEY (parent_location_id)
        REFERENCES public.location (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);
CREATE TABLE public.gameversion
(
    id bigint NOT NULL DEFAULT nextval('gameversion_id_seq'::regclass),
    name character varying(20) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT gameversion_pkey PRIMARY KEY (id)
);
CREATE TABLE public.buy
(
    id bigint NOT NULL DEFAULT nextval('buy_id_seq'::regclass),
    location_id bigint NOT NULL,
    commodity_id bigint NOT NULL,
    price numeric NOT NULL,
    scantime timestamp without time zone NOT NULL DEFAULT now(),
    gameversion_id bigint NOT NULL,
    CONSTRAINT buy_pkey PRIMARY KEY (id),
    CONSTRAINT buy_commodity_id_fkey FOREIGN KEY (commodity_id)
        REFERENCES public.commodity (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT buy_location_id_fkey FOREIGN KEY (location_id)
        REFERENCES public.location (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

纯文本：

"Agricium"  "Metal" "ArcCorp Mining Area 141"   "24.280"    "3.1.0-live.738964" "25.720"    "3.2.2-live.846694" "25.000"    "2"
"Agricium"  "Metal" "Grim HEX"  "25.000"    "3.1.0-live.738964" "36.490"    "3.0.0-live.695052" "30.983"    "6"
"Agricium"  "Metal" "Kudre Ore" "24.280"    "3.1.0-live.738964" "24.280"    "3.1.0-live.738964" "24.280"    "1"
"Agricium"  "Metal" "Levski"    "36.715"    "3.0.0-live.695052" "36.730"    "3.0.0-live.695052" "36.719"    "6"
"Agricium"  "Metal" "Port Olisar A" "0.751" "3.2.0-live.796019" "36.299"    "3.0.0-live.695052" "24.450"    "3"
"Agricium"  "Metal" "Port Olisar B" "36.229"    "3.0.0-live.695052" "36.300"    "3.0.0-live.695052" "36.276"    "3"
"Agricium"  "Metal" "Port Olisar C" "35.747"    "3.0.0-live.695052" "36.299"    "3.0.0-live.695052" "36.023"    "2"
"Agricium"  "Metal" "Port Olisar D" "36.299"    "3.0.0-live.695052" "36.300"    "3.0.0-live.695052" "36.300"    "2"
"Agricium"  "Metal" "Tram & Myers Mining"   "27.900"    "3.0.0-live.695052" "27.900"    "3.0.0-live.695052" "27.900"    "1"
"Agricultural Supply"   "Agricultural Supply"   "Hickes Research Outpost"   "0.728" "3.0.0-live.695052" "0.728" "3.0.0-live.695052" "0.728" "1"
"Agricultural Supply"   "Agricultural Supply"   "Levski"    "0.694" "3.1.0-live.738964" "0.722" "3.2.2-live.846694" "0.708" "2"
"Agricultural Supply"   "Agricultural Supply"   "Port Olisar A" "0.750" "3.2.2-live.846694" "2.025" "3.0.0-live.695052" "1.600" "3"
"Agricultural Supply"   "Agricultural Supply"   "Port Olisar B" "0.745" "3.1.0-live.738964" "2.025" "3.0.0-live.695052" "1.705" "4"
"Agricultural Supply"   "Agricultural Supply"   "Port Olisar C" "0.737" "3.1.0-live.738964" "2.025" "3.0.0-live.695052" "1.384" "4"
"Agricultural Supply"   "Agricultural Supply"   "Port Olisar D" "2.025" "3.0.0-live.695052" "2.025" "3.0.0-live.695052" "2.025" "2"
"Aluminum"  "Metal" "ArcCorp Mining Area 157"   "0.874" "3.0.0-live.695052" "0.875" "3.0.0-live.695052" "0.875" "2"
"Aluminum"  "Metal" "Grim HEX"  "1.149" "3.0.0-live.695052" "1.149" "3.0.0-live.695052" "1.149" "3"
"Aluminum"  "Metal" "Levski"    "1.143" "3.0.0-live.695052" "1.176" "3.2.2-live.846694" "1.153" "8"
postgresql aggregate