列表

详情

3126. Server Utilization Time

Table: Servers

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| server_id      | int      |
| status_time    | datetime |
| session_status | enum     |
+----------------+----------+
(server_id, status_time, session_status) is the primary key (combination of columns with unique values) for this table.
session_status is an ENUM (category) type of ('start', 'stop').
Each row of this table contains server_id, status_time, and session_status.

Write a solution to find the total time when servers were running. The output should be rounded down to the nearest number of full days.

Return the result table in any order.

The result format is in the following example.

 

Example:

Input:

Servers table:

+-----------+---------------------+----------------+
| server_id | status_time         | session_status |
+-----------+---------------------+----------------+
| 3         | 2023-11-04 16:29:47 | start          |
| 3         | 2023-11-05 01:49:47 | stop           |
| 3         | 2023-11-25 01:37:08 | start          |
| 3         | 2023-11-25 03:50:08 | stop           |
| 1         | 2023-11-13 03:05:31 | start          |
| 1         | 2023-11-13 11:10:31 | stop           |
| 4         | 2023-11-29 15:11:17 | start          |
| 4         | 2023-11-29 15:42:17 | stop           |
| 4         | 2023-11-20 00:31:44 | start          |
| 4         | 2023-11-20 07:03:44 | stop           |
| 1         | 2023-11-20 00:27:11 | start          |
| 1         | 2023-11-20 01:41:11 | stop           |
| 3         | 2023-11-04 23:16:48 | start          |
| 3         | 2023-11-05 01:15:48 | stop           |
| 4         | 2023-11-30 15:09:18 | start          |
| 4         | 2023-11-30 20:48:18 | stop           |
| 4         | 2023-11-25 21:09:06 | start          |
| 4         | 2023-11-26 04:58:06 | stop           |
| 5         | 2023-11-16 19:42:22 | start          |
| 5         | 2023-11-16 21:08:22 | stop           |
+-----------+---------------------+----------------+

Output:

+-------------------+
| total_uptime_days |
+-------------------+
| 1                 |
+-------------------+

Explanation:

The accumulated runtime for all servers totals approximately 44.46 hours, equivalent to one full day plus some additional hours. However, since we consider only full days, the final output is rounded to 1 full day.

原站题解

去查看

上次编辑到这里，代码来自缓存 点击恢复默认模板
# Write your MySQL query statement below

mysql 解法, 执行用时: 404 ms, 内存消耗: 0 B, 提交时间: 2024-05-01 10:19:27 

# Write your MySQL query statement below
WITH r AS (-- 以每台服务器按时间状态排序 status_time  00:00:00 -> 23:59:59
    SELECT 
        *,
        RANK() OVER (PARTITION BY server_id ORDER BY status_time, session_status) AS rk 
    FROM 
        Servers
)

SELECT 
    FLOOR(SUM(duration) / 86400) AS total_uptime_days -- 运行天数
FROM 
(
    SELECT 
        TIMESTAMPDIFF(SECOND, a.status_time, b.status_time) AS duration -- 计算毛得到每次开始结果的时间
    FROM 
        r AS a, 
        r AS b 
    WHERE 
        a.server_id = b.server_id AND a.session_status = 'start' AND a.rk = b.rk - 1
) AS t

mysql 解法, 执行用时: 367 ms, 内存消耗: 0 B, 提交时间: 2024-05-01 10:18:46 

# Write your MySQL query statement below
SELECT FLOOR(sum(TIMESTAMPDIFF(SECOND , status_time, next_status_time))/(24*60*60)) as total_uptime_days FROM (
    SELECT
        session_status,
        status_time,
        LEAD(status_time) OVER (PARTITION BY server_id ORDER BY status_time) AS next_status_time
    FROM
        Servers) T
where session_status = 'start';

上一题

© 2024 leetcode.cn