我有3个表(订单,用户和用户工作时间)
,我正在尝试汇总一些总数,并使用单个查询计算一段时间内每小时的劳动力成本。
例如:人工成本=((用户工作时间*使用率)/总数)
所需时间,按小时列出,如下所示:
Hour Total Qty LC
09:00 - 10:00 $53 18 $25
10:00 - 11:00 $60 22 $24
12:00 - 13:00 ....
13:00 - 14:00 ....
所需的周期来自DATETIME k_orders created_on
每小时刹车(10:00 - 11:00)来自(a DATETIME) k_orders created_on
小时费率来自(FLOAT,例如:4.52)k_users hourly_rate
工作时间来自(a DATETIME) k_users_clock_inout clock_in/clock_out
不同的用户有不同的速率,每个用户需要单独计算小时数
也可以是一个月,或者例如过去2个月的所有星期一。
表是:
CREATE TABLE IF NOT EXISTS `k_orders` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`created_on` datetime NOT NULL,
`store` int(20) NOT NULL,
`canceled` int(1) NOT NULL DEFAULT '0',
`paid` int(1) NOT NULL DEFAULT '0',
`total` decimal(10,2) NOT NULL,
`total_qty` int(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `k_users_clock_inout` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`store_id` int(20) NOT NULL,
`user_id` int(20) NOT NULL,
`clock_in` datetime NOT NULL,
`clock_out` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `k_users` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`hourly_rate` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
一个问题:这在单个查询中可能吗?
每次我试着去做,我卡住了,比如上次卡住:
SELECT HOUR(`created_on`) hour, SUM(O.total) total, SUM(O.total_qty) qty, (SUM(O.total) / SUM(O.total_qty)) average,
(
SELECT SUM(total_rate / total) FROM (
SELECT SUM(user_rate) total_rate FROM (
SELECT SUM( (TIMESTAMPDIFF( MINUTE , C.clock_in, C.clock_out ) / 60) * U.hourly_rate) user_rate
FROM `k_users_clock_inout` C
LEFT JOIN k_users U ON U.id = C.user_id
WHERE
TIME(C.clock_in) BETWEEN hour AND (hour + 1)
AND
TIME(C.clock_out) BETWEEN hour AND (hour + 1)
AND U.hourly_rate > 0
GROUP BY C.user_id
) as subsubquery
) as subquery
) lc
FROM `k_orders` O
WHERE
O.paid = 1 AND
O.canceled = 0 AND
O.created_on BETWEEN '2016-07-10 00:00:00' AND '2016-08-10 00:00:00' AND
O.store IN (3)
GROUP BY HOUR(`created_on`)
ORDER BY HOUR(`created_on`)
,当我在哪里部分,试图添加间隔到当前小时TIME(C.clock_in)之间的小时和(小时+ 1)(不工作,我知道)我意识到我不能在多个子查询中使用别名…我又卡住了,但是截止日期没有提前,所以任何帮助都是非常感谢的。
一个月的查询结果示例(O.created_on BETWEEN '2016-07-10 00:00:00' AND '2016-08-10 00:00:00'):
hour total qty average lc
7 1.80 2 0.900000 329.84888887555553
8 2.20 2 1.100000 269.87636362545453
9 2.70 3 0.900000 164.92444443777777
10 71.33 20 3.566500 22.83569230676923
11 321.66 63 5.105714 34.1222988491954
........
21 834.38 174 4.795287 28.272761903619045
22 77.85 14 5.560714 8.457663817321937
演示SQL data: https://www.dropbox.com/s/tz2oeecfn81szjk/atest.sql?dl=0
我尝试了很多你的查询和修改像这样。但是你必须简化这个查询。尽管它给出了结果,但这不是正确的方法。
SET @hor :=
(SELECT
GROUP_CONCAT(HOUR(`created_on`))
FROM
k_orders o
WHERE O.paid = 1
AND O.canceled = 0
AND O.created_on BETWEEN '2016-09-01 00:00:00'
AND '2016-10-01 00:00:00'
AND O.store IN (3)) ;
SELECT HOUR(`created_on`) HOUR1, SUM(O.total) total, SUM(O.total_qty) qty, (SUM(O.total) / SUM(O.total_qty)) average,
(
SELECT SUM(total_rate / total) FROM (
SELECT SUM(user_rate) total_rate FROM (
SELECT SUM( (TIMESTAMPDIFF( MINUTE , C.clock_in, C.clock_out ) / 60) * U.hourly_rate) user_rate
FROM `k_users_clock_inout` C
LEFT JOIN k_users U ON U.id = C.user_id
WHERE
HOUR(C.clock_in) BETWEEN (SELECT HOUR(created_on) FROM k_orders WHERE id = FIND_IN_SET(HOUR(created_on),@hor) LIMIT 1)
AND ((SELECT HOUR(created_on) FROM k_orders WHERE id = FIND_IN_SET(HOUR(created_on),@hor) LIMIT 1) + 1)
AND
HOUR(C.clock_out) BETWEEN (SELECT HOUR(created_on) FROM k_orders WHERE id = FIND_IN_SET(HOUR(created_on),@hor) LIMIT 1 ) AND ((SELECT HOUR(created_on) FROM k_orders WHERE id = FIND_IN_SET(HOUR(created_on),@hor) LIMIT 1) + 1)
AND U.hourly_rate > 0 AND REPLACE(@hor, CONCAT((SELECT HOUR(created_on) FROM k_orders WHERE id = FIND_IN_SET(HOUR(created_on),@hor) LIMIT 1),',') , '')
GROUP BY C.user_id
) AS subsubquery
) AS subquery
) lc
FROM `k_orders` O
WHERE
O.paid = 1 AND
O.canceled = 0 AND
O.created_on BETWEEN '2016-09-01 00:00:00' AND '2016-10-01 00:00:00' AND
O.store IN (3)
GROUP BY HOUR(`created_on`)
ORDER BY HOUR(`created_on`)