需要MySQL查询和组建议

Question

我尝试了一段时间来运行一个查询，但这比我想象的要困难。我有以下3张表：

Stores:
+--+-------+--------------+-------+---------+---------+----+---------------+---+------+
|ID|Company|Address       |PostCod|Latitude |Longitude|Stat|   Division    |Gro| When |
+--+-------+--------------+-------+---------+---------+----+---------------+---+------+
|17|Company|Site Address 3|WF1 5NT|53.666340|-1.487857|OPEN|Test Division 2|arl|2014-01-31 14:36:04
|18|Company|Site Address 3|WF1 5NT|53.666340|-1.487857|OPEN|Test Division 2|arl|2014-01-31 14:36:04
|19|Company|Site Address 3|WF1 5NT|53.666340|-1.487857|OPEN|Test Division 2|arl|2014-01-31 14:36:04
|20|Company|Site Address 3|WF1 5NT|53.666340|-1.487857|OPEN|Test Division 2|arl|2014-01-31 14:36:04
+--+-------+--------------+-------+---------+---------+----+---------------+---+------+

Jobs:
+--------+-------------------------------------------+-------+------------+------+--------+
| client | description                               | freq  | from       | till |job_id |
+--------+-------------------------------------------+-------+------------+------+--------+
|     17 | Weekly external and internal window clean |  7    | 2013-10-01 | NULL |    17 |
|     18 | Weekly external and internal window clean |  7    | 2013-10-01 | NULL |    18 |
|     19 | Weekly external and internal window clean |  7    | 2013-10-01 | NULL |    19 |
|     20 | Weekly external and internal window clean |  7    | 2013-10-01 | NULL |    20 |
|     17 | 4 weekly fascia and upper floor windows   | 28    | 2013-10-01 | NULL |   645 |
|     18 | 4 weekly fascia and upper floor windows   | 28    | 2013-10-01 | NULL |   646 |
|     19 | 4 weekly fascia and upper floor windows   | 28    | 2013-10-01 | NULL |   647 |
|     20 | 4 weekly fascia and upper floor windows   | 28    | 2013-10-01 | NULL |   648 |
+--------+-------------------------------------------+-------+------------+------+--------+


Job_hist
+------+-------------------+-----------------+---------+------+--------------+-----------------------------------------+-------------------------------+------+-----+-----------------------+------+
|Job_id|       last        |       user      | Company | LINK | Signedy_by   |       Job Description                   |          Address              | Accu |Days | Possition             |Uniqid|
+------+-------------------+-----------------+---------+------+--------------+-----------------------------------------+-------------------------------+------+-----+-----------------------+------+
|17    |2013-10-01 09:35:37|ARL_Operative_013|Santander| LINK |tony moore    |Weekly external window clean             | AN WELLING 14 BR (DA16 3PP)   |739.00|10   |Branch Manager         |132   |
|20    |2013-10-02 12:27:51|ARL_Operative_013|Santander| LINK |alex goodman  |Weekly external window clean             | AN HAROLD HILL 69 FR (RM3 8XA)|55.00 |6    |Store Assistant Manager|268   |
|19    |2013-10-03 09:14:19|ARL_Operative_013|Santander| LINK |darren pickett|Weekly external window clean             | AN WOOLWICH 41 PS (SE18 6JD)  |50.00 |5    |Other                  |332   |
|18    |2013-10-03 09:54:49|ARL_Operative_013|Santander| LINK |james lawrence|Weekly external window clean             | AN ELTHAM 73 EHS (SE9 1UW)    |49.00 |7    |Other                  |346   |
|17    |2013-10-08 09:05:16|ARL_Operative_013|Santander| LINK |tony moore    |Weekly external and internal window clean| AN WELLING 14 BR (DA16 3PP)   |67.00 |6    |Branch Manager         |697   |
+------+-------------------+-----------------+---------+------+--------------+-----------------------------------------+-------------------------------+------+-----+-----------------------+------+

我需要写一个查询，它根据除法进行一些性能计算。目标是：

1. 计算给定时间框架内计划的清理次数，(简单版本)取开始日期和结束日期，计算天数并与stores.freq除以。

1. 在此期间，有多少计划清除了按时完成的任务。我们从job_hist ( job_hist.jod_id=jobs.job_id和job_hist.last位于区间内，job_hist.days <= freq )获取这些数据。

1. 清洁按时完成的百分比

1. 有多少次清理到迟做的地方(相同的逻辑，如在第2点，只是job_hist> freq)

1. 清理时间较晚的百分比

1. 有多少人错过了(按时完成)

1. 漏报百分比

所有这些都应该按照除法进行分组，因此结果应该如下所示：

+------------------------------+---------+------+-----------------+---------+--------------------+------+-----------------+
|division                      |scheduled|ontime|ontime_percentage|completed|completed_percentage|missed|missed_percentage
+------------------------------+---------+------+-----------------+---------+--------------------+------+-----------------+
|fsdfoihsdfljksdlgjdfsligsgfsfd|16282    |10404 |63.90%           |10825    |66.48%              |5457  |33.52%
|Test Division 2               |259      |129   |49.81%           |133      |51.35%              |126   |48.65%
|Test Division 3               |30       |15    |50.00%           |15       |50.00%              |15    |50.00%
+------------------------------+---------+------+-----------------+---------+--------------------+------+-----------------+

现在，我不太擅长SQL查询，但我成功地将以下查询组合在一起：

SELECT `stores`.`division`, 
SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) as `scheduled`, 
count(`name`)  as `ontime`, 
concat(round(( count(`name`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS ontime_percentage,
 count(`user`) as `completed`,
concat(round(( count(`user`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS completed_percentage,
(SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-count(`user`)) as `missed`,
concat(round(( (SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-count(`user`)) /SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS missed_percentage
from `stores` left join `jobs` on `stores`.`id`=`jobs`.`client`
left join (select `user`,`job_id` from `job_hist` where  `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till') as `myquery` on `myquery`.`job_id`=`jobs`.`job_id`
left join (select `name`,`job_hist`.`job_id` from `job_hist` left join `jobs` on `jobs`.`job_id`=`job_hist`.`job_id` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' and `job_hist`.`days`<=`jobs`.`freq`) as `myquery2` on `myquery2`.`job_id`=`jobs`.`job_id`
where `stores`.`owner`='$group'
group by `division`

$till、$from、$group是一些PHP变量，$till和$from是字符串格式的日期，$group是字符串。

现在查询运行得很好，但是(总是有一个“但是”)，调度字段正在总结不同的值，我的意思是有时候它是值的两倍，有时是它的三倍。

我读过THIS，但我还是没能理解它。我或多或少地明白，我的一些结果是加倍的，但仍然无法弄清楚我需要改变什么。

在任何人建议加入stores.division和job_hist.division之间的除法之前，这是不可行的，因为分区可能会随着时间的变化而变化，它们会在stores中更新，而不是在job_hist中更新。job_hist不应该在任何时候被修改，即只包含历史数据。

更新

好的，我设法调整了我的查询，直到现在，我似乎得到了正确的数据，但我需要运行更多的测试，以确保在发布我自己的答案之前。无论如何，下面是我更新的查询：

SELECT `stores`.`division`, 
SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)   as `scheduled`,
sum(`ontime`), 
concat(round(( sum(`ontime`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS ontime_percentage, 
sum(`completed`-`ontime`), 
concat(round(( sum(`completed`-`ontime`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS completed_percentage, 
(SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-sum(`completed`)) as `missed`, 
concat(round(( (SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-sum(`completed`)) /SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS missed_percentage from `stores` 
left join `jobs` on `stores`.`id`=`jobs`.`client` 
left join (select count(`user`) as `completed`,`job_id` from `job_hist` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' group by `job_id`) as `myquery` on `myquery`.`job_id`=`jobs`.`job_id` 
left join (select count(`name`) as `ontime`,`job_hist`.`job_id` from `job_hist` left join `jobs` on `jobs`.`job_id`=`job_hist`.`job_id` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' and `job_hist`.`days`<=`jobs`.`freq` group by `job_id`) as `myquery2` on `myquery2`.`job_id`=`jobs`.`job_id` 
where `stores`.`owner`='$group' 
group by `stores`.`division`

我愿意用一种更好的方式来运行这个查询。

Answer 1

问题出在分组上，下面是正确返回数据的查询：

SELECT `stores`.`division`, 
SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)   as `scheduled`,
sum(`ontime`), 
concat(round(( sum(`ontime`)/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS ontime_percentage, 
sum(`completed`)-sum(`ontime`), 
concat(round(( (sum(`completed`)-sum(`ontime`))/SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS completed_percentage, 
(SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-sum(`completed`)) as `missed`, 
concat(round(( (SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`)-sum(`completed`)) /SUM(DATEDIFF(LEAST(IFNULL(`till`,CURDATE()),'$till'),GREATEST(`from`,'$from')) DIV `freq`) * 100 ),2),'%') AS missed_percentage from `stores` 
left join `jobs` on `stores`.`id`=`jobs`.`client` 
left join (select count(`user`) as `completed`,`job_id` from `job_hist` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' group by `job_id`) as `myquery` on `myquery`.`job_id`=`jobs`.`job_id` 
left join (select count(`name`) as `ontime`,`job_hist`.`job_id` from `job_hist` left join `jobs` on `jobs`.`job_id`=`job_hist`.`job_id` where `job_hist`.`last`>='$from' and `job_hist`.`last`<='$till' and `job_hist`.`days`<=`jobs`.`freq` group by `job_id`) as `myquery2` on `myquery2`.`job_id`=`jobs`.`job_id` 
where `stores`.`owner`='$group' 
group by `stores`.`division`

如果有人有更好的解决方案，请张贴。