mysql - 根据每个唯一的列值计算百分比

Question

抱歉，帖子太长了。我有一个包含以下列的表格
候选人姓名、选区、政党、结果

CREATE TABLE `parlia`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CANDIDATE_NAME` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
  `REGION` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
  `CONSTITUENCY` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
  `PARTY` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
  `RESULT` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
) ENGINE = InnoDB AUTO_INCREMENT = 295 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (31, 'OPPONG SAMUEL', 'ASHANTI', 'AKROFUOM', 'NDC', 7484);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (32, 'KWABENA APPIAH-AKROFUOM', 'ASHANTI', 'AKROFUOM', 'NPP', 10249);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (34, 'NATHONY KWAKYE-AMEYAW', 'ASHANTI', 'AKROFUOM', 'PPP', 129);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (49, 'HON ALHAJI MOHAMMED MUNTAKA MUBARAK', 'ASHANTI', 'ASAWASE', 'NDC', 43917);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (50, 'NANA OKKERE TAWIAH ANTWI', 'ASHANTI', 'ASAWASE', 'NPP', 31013);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (51, 'JERRY JOSEPH QUAYSON', 'ASHANTI', 'ASAWASE', 'PPP', 458);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (90, 'DACOSTA KOFI YEBOAH', 'ASHANTI', 'BEKWAI', 'NDP', 92);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (91, 'AKWASI GYAMFI', 'ASHANTI', 'BEKWAI', 'INDP', 656);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (92, 'DR.JOE OTENG ADJEI', 'ASHANTI', 'BOSOMTWE', 'NDC', 17235);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (93, 'HON. SIMON OSEI MENSAH', 'ASHANTI', 'BOSOMTWE', 'NPP', 34790);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (95, 'YAW WIREDU', 'ASHANTI', 'BOSOMTWE', 'PNC', 58);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (96, 'HON KWAME ADARKWAH', 'ASHANTI', 'BOSOME FREHO', 'NDC', 5467);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (97, 'KWADWO KYEI FRIMPONG', 'ASHANTI', 'BOSOME FREHO', 'NPP', 14293);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (98, 'OWUSU AUGUSTINE', 'ASHANTI', 'BOSOME FREHO', 'CPP', 143);

我想将每个候选人的百分比份额计算到名为 的另一列中。分享 基于各个选区的结果的总和。下面是一个没有 share 列的查询。查看链接图像

SELECT CANDIDATE_NAME, CONSTITUENCY, PARTY, RESULT
FROM parlia
WHERE REGION = 'ASHANTI'

query results- Linked Image
我使用 ROUND 函数执行此操作，但我获得的 %Percentage 份额使用 RESULTS 列中所有值的总和进行计算。我的目标是根据每个选区的 SUM(RESULTS) 获得每个 PARTY 的 SHARE。

SELECT CANDIDATE_NAME, CONSTITUENCY, PARTY, RESULT,
ROUND((RESULT/(SELECT SUM(RESULT) FROM parlia)) * 100,2) AS `SHARE`
FROM parlia
WHERE REGION = 'ASHANTI'

Answer 1

您可以尝试在此处使用分析函数:

SELECT *, ROUND(100.0 * RESULT / SUM(RESULT) OVER (PARTITION BY CONSTITUENCY), 2) AS SHARE_PER_CONSTITUENCY
FROM e_parlia_election;

窗口函数调用 SUM以上是每个选区的总结果。然后我们使用这个总数对每条记录的结果进行标准化，并转换为两位小数百分比。