0

I have table mysql with column date, time, subject, description and value. I want to know the value at current day is increase or degrade from the value at last 7 day in the same time.

Date            Time            Subject     Description         Value|
---------------------------------------------------------------------|
2023-08-10  20:03:00    DNS Gn/BRN  DNS mem avail real   1089350|
2023-08-10  20:03:00    DNS Gn2     DNS mem total real   65601700|
2023-08-10  20:03:00    DNS Gn      DNS mem total swap   25165800|
    :         :            :              :                  :
    :         :            :              :                  :
2023-08-17  20:03:00    DNS Gn      DNS mem avail real   878044032|
2023-08-17  20:03:00    DNS Gn/BRN  DNS mem avail real   8567809|
2023-08-10  20:03:00    DNS Gn2     DNS mem total real   6780408|

The final expecting result:

Date        Time         Subject      Subject1           Value     Status|
------------------------------------------------------------------------|
2023-08-10  20:03:00    DNS Gn/BRN1 DNS mem avail real   10893500     ?|
2023-08-10  20:03:00    DNS Gn2     DNS mem total real   65601700     ?|
2023-08-10  20:03:00    DNS Gn      DNS mem total swap   25165800     ?|
    :          :            :            :                  :   |
    :          :            :            :                  :   |
2023-08-17  20:03:00    DNS Gn      DNS mem avail real   87804408   -30.84%| --> [(65601700-87804408)/65601700]
2023-08-17  20:03:00    DNS Gn/BRN1 DNS mem avail real   8567809     21.34%| --> [(10893500-8567809)/10893500]
2023-08-17  20:03:00    DNS Gn2     DNS mem avail real   6780408     75.06%|--> [(25165800-6780408)/25165800]

How to get the status of each Subject?

Meanwhile I use command LEFT JOIN, but the result was unexpected ...

Thanks

Improve this question
2
  • .. FROM table t1 [LEFT] JOIN table t2 ON t1.date = t2.date + INTERVAL 1 WEEK ... Commented Aug 15, 2023 at 5:05
  • See Akina's comment, but also compare the times. Commented Sep 14, 2023 at 1:15
Related questions
1056
How do you set a default value for a MySQL Datetime column?
1063
How can I find all the tables in MySQL with specific column names in them?
920
How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.