extracting and sorting mysql maximums

I would like to ask you an additional question regarding the contents of your reply.
mysql Work Data ID Extraction Across Dates

The ID is now displayed every 15 minutes as shown below.

この I would like to summarize the IDs displayed every 15 minutes for each USER_NAME as follows.

US I want to sort them in order of START_TIME after putting them together by USER_NAME.

As for に, we can display the maximum value in Excel PIVOT, but we would like to display the maximum ID on SQL.
②It doesn't come out well even if I put MIN(START_TIME) (only one line of data is displayed).
①Is it possible to display を on SQL?

Original data
SELECT distinct
        Prefectural code,
        City Code,
       USER_NAME,
       ID,
       case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo'), '%H:%i')>'00:00' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo'), '%H:%i')>INVER ID 15_INVED)
            case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')>'00:15' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo', '%i')>INVERTER ID:30_INVED))
case when(
    (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')>'00:00'
     OR
     DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i') = '00:00'
     )
     and
     DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')<'00:15')
     then SCHEDULE_INTERVAL_TYPE_ID end as '00:00',
/* Abbreviated */
case when(
    (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')>'23:45'
     OR
     DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00', 'Asia/Tokyo', '%H:%i') = '00:00'
    )
    and
    DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00', 'Asia/Tokyo', '%H:%i')<'23:59')
    then SCHEDULE_INTERVAL_TYPE_ID end as '23:45'
FROM Table 1
WHERE 
    DATE_FORMAT(CONVERT_TIME, '+00:00', 'Asia/Tokyo', '%Y%m%d') = DATE_FORMAT(CONVERT_TZ(CURRENT_TIMESTAMP, '+00:00', 'Asia/Tokyo', '%Y%m%d');

mysql sql

2022-09-30 21:26

2 Answers

# The content of the comment seemed to be a different question, so I will write it as a separate answer.

We assumed that the question in the previous comment was to obtain the maximum number of IDs (by user) in the time zone (separated by 15 compartments.Is this the SQL statement?

/*Image*/
    (
    WITH tblas (whole SELECT statement in the previous answer)
    select '00:00' as "time", "t00:00" as "id", count("t00:00") from tbl where "t00:00" is not null group by "id"
    union
    select'00:15' as "time", "t00:15" as "id", count("t00:15") from tbl where "t00:15" is not null group by "id"
    union
    /* Abbreviated */
    union
    select '23:45' as "time", "t23:45" as "id", count("t23:45") from tbl where "t23:45" is not null group by "id"
    order by "time", "id";

    /* Retrieved Image*/
     time | id | count
    -------+----+-------
     /* Abbreviated */
     10:30 |  1 |     2
     10:30 |  3 |     1
     10:45 |  1 |     2
     10:45 |  3 |     1
     11:00 |  1 |     1
     11:00 |  3 |     1
     /* Omitted */

# Since the time zone information has been converted to column names and cannot be written to SELECT conditions, we use UNION to retrieve it as count by time zone.Personally, I think it's a bad SQL statement, so I recommend that you review the original table structure to make it easier to retrieve.

If the WITH clause is not available, define as a VIEW (for example, tbl) and refer to SELECT (for example, select~from tbl where "t00:00" is not null) or
select~from tbl directly in tbl of select~from tbl (for example, select~from (the entire SELECT statement)t).

/*Images to be written directly*/
    select '00:00' as "time", "t00:00" as "id", count("t00:00") from (whole SELECT statement in previous answer) tbl where "t00:00" is not null group by id
    union
    select'00:15' as "time", "t00:15" as "id", count("t00:15") from (whole SELECT statement in previous answer) tbl where "t00:15" is not null group by id
    union
    /* Abbreviated */
    union
    select '23:45' as "time", "t23:45" as "id", count("t23:45") from (whole SELECT statement in previous answer) tbl where "t23:45" is not null group by id
    ;

# With MySQL, there seems to be a way to create a temporary table with CREATE TEMPORARY TABLE instead of VIEW, but with this, UNION and JOIN cannot be used for my idea.

2022-09-30 21:26

# Performance is negligible.

For は, you should use the results of the select statement in the question as a temporary table and obtain the maximum value for each column (from 00:00 to 23:45).

/*Image*/
    select user_name, max("00:00"), max("00:15"), /* Abbreviated */, max("23:45")
    from (whole question select statement) t
    group by t.user_name;

As for は, I do not understand the intent of the question, so I will refrain from answering.(I didn't get START_TIME in the select statement in the question, so I didn't know the intention of rearranging.)

2022-09-30 21:26

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656