基于每个唯一列值计算百分比

Question

基于每个唯一列值计算百分比

4

很抱歉内容有点长。我有一个包含以下列的表格：

候选人姓名，选区，政党，结果

这些与it技术无关。

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的地方计算每个候选人在各个选区结果的总和基础上的百分比份额。以下是没有SHARE列的查询。请参见链接中的图像。

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

查询结果- 链接图片

我使用ROUND函数完成了这个操作，但是我得到的百分比份额是使用RESULTS列中所有值的总和进行计算的。我的目标是根据每个选区的SUM(RESULTS)获取每个政党的份额。

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

- Enyo

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tim Biegeleisen · Accepted Answer

你可以尝试在这里使用分析函数：

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

以上的窗口函数调用SUM用于找到每个选区的总结果。然后，我们使用这个总数来归一化每个记录的结果，并将其转换为两位小数的百分比。