在MySQL中的别名和进行数学运算。

huangapple go评论65阅读模式
英文:

Aliases in MySQL and do maths

问题

我需要计算用户的有效性以在报告中显示,我编写了以下语句:

SELECT COUNT(`call_id`) AS logged 
FROM `tbl_calls`
WHERE `user_id_attended_by` = 24
AND YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND `fk_supplier_id` = 3
UNION ALL 
SELECT COUNT(call_id) AS ACK
FROM `tbl_calls`
WHERE YEAR(`date_logged`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(`date_logged`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND `repaired_by` = 24
AND `fk_supplier_id` = 3

上述语句的输出如下所示:

在MySQL中的别名和进行数学运算。

我需要获取图片中两个总数的百分比,以添加到报告中以获取用户的绩效。我尝试创建一个临时表并将数据输入其中,但在xampp中无法使其正常工作。

英文:

I need to workout the effectiveness of a user to display in a report, i complied the following statement

SELECT COUNT(`call_id`) AS logged 
            FROM `tbl_calls`
            WHERE `user_id_attended_by` = 24
            AND YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
            AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
            AND `fk_supplier_id` = 3
	UNION ALL 
        SELECT COUNT(call_id) AS ACK
            FROM `tbl_calls`
            WHERE YEAR(`date_logged`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
            AND MONTH(`date_logged`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
            AND `repaired_by` = 24
            AND `fk_supplier_id` = 3

The output for the above motioned statement as follows,

在MySQL中的别名和进行数学运算。

I need to get the percentages of the two totals in the pictures to add it to a report to get the users performance, I tried to create a temp table and entering the data into it but could not get it working in xampp.

答案1

得分: 1

SELECT logged,
       100*logged/(logged+ack) percent_logged,
       ack,
       100*ack/(logged+ack) percent_ack
FROM (
    SELECT SUM(`user_id_attended_by` = 24) AS logged,
           SUM(`repaired_by` = 24) AS ack
    FROM `tbl_calls`
    WHERE YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
      AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
      AND `fk_supplier_id` = 3
    ) subquery
英文:
SELECT logged,
       100*logged/(logged+ack) percent_logged,
       ack,
       100*ack/(logged+ack) percent_ack
FROM (
    SELECT SUM(`user_id_attended_by` = 24) AS logged,
           SUM(repaired_by` = 24) AS ack
    FROM `tbl_calls`
    WHERE YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
      AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
      AND `fk_supplier_id` = 3
    ) subquery

?

答案2

得分: 1

我不知道date_ack_by_techdate_logged之间的区别是什么,但我假设你使用它们有原因。所以,我没有尝试将你的查询合并在一起。如果不需要区分这两个日期,那么Akina的方法更可取。

我建议放弃使用MONTH()YEAR(),而是使用日期范围。这将使这些条件成为可搜索谓词,这在这种情况下可能有帮助,也消除了当前的一月问题(2023年1月返回2022年12月,而2023年2月返回2022年1月 - 提前一个月或13个月)。

如果您想继续使用MONTH()YEAR()的方法,您应该将年份标准更改为:

YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 13 MONTH)
英文:

I have no idea what the difference is between date_ack_by_tech and date_logged, but I assume you have a reason for using them. So, I have not tried to smudge your queries together. If there is no need to differentiate between these two dates, then Akina's method is preferable.

I would drop the use of MONTH() and YEAR() and instead use a date range. This will make these criteria sargable, which may or may not be helpful in this case. But it also does away with your current January issue (Jan 2023 returns Dec 2022, whereas Feb 2023 returns Jan 2022 - 1 month or 13 months).

SELECT (
    SELECT COUNT(`call_id`) AS logged 
    FROM `tbl_calls`
    WHERE `date_ack_by_tech` BETWEEN CURRENT_DATE - INTERVAL 13 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY
                                 AND CURRENT_DATE - INTERVAL 12 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY - INTERVAL 1 SECOND
    AND `user_id_attended_by` = 24
    AND `fk_supplier_id` = 3
) AS logged, (
    SELECT COUNT(call_id) AS ack
    FROM `tbl_calls`
    WHERE `date_logged` BETWEEN CURRENT_DATE - INTERVAL 13 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY
					        AND CURRENT_DATE - INTERVAL 12 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY - INTERVAL 1 SECOND
    AND `repaired_by` = 24
    AND `fk_supplier_id` = 3
) AS ack,
(SELECT ROUND(100 * logged / (logged + ack), 2)) AS percent_logged,
(SELECT ROUND(100 * ack / (logged + ack), 2)) AS percent_ack;

If you want to continue using your MONTH() and YEAR() method, you should change the year criterion to:

YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 13 MONTH)

huangapple
  • 本文由 发表于 2023年2月19日 18:42:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/75499542.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定