How to get recent date field value after group-CodePudding

Say there's a table like:

#  spend_daily_level

CREATE TABLE `spend_daily_level` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `date` date NOT NULL,
  `system_value` decimal(16,2) NOT NULL,
  `checked_value` decimal(16,2) NOT NULL,
  `account_id` int(11) NOT NULL COMMENT,
  `sale_leader_id` int(11) DEFAULT NULL ,
  `account_status` tinyint(3) DEFAULT,
  `growth` tinyint(1) NOT NULL DEFAULT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `spend_daily_level_date_account_id_f38b1186_uniq` (`date`,`account_id`),
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8


#  base data

id |active|created_time              |updated_time              |date      |system_value|checked_value|account_id|sale_leader_id|account_status|growth|
--- ------ -------------------------- -------------------------- ---------- ------------ ------------- ---------- -------------- -------------- ------ 
  1|     1|2020-05-22 14:27:04.35329 |2020-05-29 14:27:04.353334|2020-05-22|       18.34|         0.00|     83266|           683|             1|     0|
  2|     1|2020-05-23 14:27:04.369496|2020-05-29 14:27:04.369531|2020-05-23|       30.63|         0.00|     83266|           521|             2|     0|
  3|     1|2020-05-24 14:27:04.376334|2020-05-29 14:27:04.376371|2020-05-24|       36.01|         0.00|     83266|           521|             3|     0|
  4|     1|2020-05-25 14:27:04.382636|2020-05-29 14:27:04.382671|2020-05-25|       55.87|         0.00|     83266|           521|             1|     0|
  5|     1|2020-05-22 14:27:04.388927|2020-05-29 14:27:04.388963|2020-05-22|       63.26|         0.00|     82158|           521|             1|     0|
  6|     1|2020-05-23 14:27:04.395969|2020-05-29 14:27:04.396012|2020-05-23|       48.54|         0.00|     82158|           521|             1|     0|
  7|     1|2020-05-24 14:27:04.402606|2020-05-29 14:27:04.402644|2020-05-24|       58.52|         0.00|     82158|           521|             2|     0|
  8|     1|2020-05-25 14:27:04.409069|2020-05-29 14:27:04.409104|2020-05-25|       43.20|         0.00|     82158|           521|             3|     0|


#  expected output

id |active|created_time              |updated_time              |date      |system_value|checked_value|account_id|sale_leader_id|account_status|growth|
--- ------ -------------------------- -------------------------- ---------- ------------ ------------- ---------- -------------- -------------- ------ 
  1|     1| 2020-05-22 14:27:04.35329|2020-05-29 14:27:04.353334|2020-05-22|       18.34|         0.00|     83266|           683|             1|     0|
  2|     1|2020-05-23 14:27:04.369496|2020-05-29 14:27:04.369531|2020-05-23|       30.63|         0.00|     83266|           521|             1|     0|
  3|     1|2020-05-24 14:27:04.376334|2020-05-29 14:27:04.376371|2020-05-24|       36.01|         0.00|     83266|           521|             1|     0|
  4|     1|2020-05-25 14:27:04.382636|2020-05-29 14:27:04.382671|2020-05-25|       55.87|         0.00|     83266|           521|             1|     0|
  5|     1|2020-05-22 14:27:04.388927|2020-05-29 14:27:04.388963|2020-05-22|       63.26|         0.00|     82158|           521|             3|     0|
  6|     1|2020-05-23 14:27:04.395969|2020-05-29 14:27:04.396012|2020-05-23|       48.54|         0.00|     82158|           521|             3|     0|
  7|     1|2020-05-24 14:27:04.402606|2020-05-29 14:27:04.402644|2020-05-24|       58.52|         0.00|     82158|           521|             3|     0|
  8|     1|2020-05-25 14:27:04.409069|2020-05-29 14:27:04.409104|2020-05-25|       43.20|         0.00|     82158|           521|             3|     0|

And I want to get the recent account_status of each account from table spend_daily_level.

My trial is to use SUBSTRING_INDEX( GROUP_CONCAT(DISTINCT account_status ORDER BY date DESC), "," , 1) AS account_status

SELECT 

 s.account_id,
 s.date,
 s.system_value,
 t1.account_status


 from spend_daily_level s 

JOIN 

(SELECT
        account_id,
        SUBSTRING_INDEX(
                    GROUP_CONCAT(DISTINCT account_status ORDER BY date DESC), "," , 1) AS account_status
    FROM
        spend_daily_level
    GROUP BY
        account_id 
) t1 

ON s.account_id = t1.account_id

Update more info

I'm using MYSQL 5.7

Is there any better way?

Thanks

CodePudding user response：

In MySQL 8 you can use the last_value() window function which would make that much simpler:

SELECT id,
       ...
       sale_leader_id,
       last_value(account_status) OVER (PARTITION BY account_id
                                        ORDER BY date
                                        ROWS BETWEEN UNBOUNDED PRECEDING
                                                     AND UNBOUNDED FOLLOWING) AS account_status,
       growth
       FROM spend_daily_level;

CodePudding user response：

use a subquery to select

SELECT
 s.account_id,
 s.date,
 s.system_value,
 ( SELECT t.account_status 
   FROM spend_daily_level t 
   WHERE t.account_id = s.account_id 
   ORDER BY t.date DESC 
   LIMIT 1
 ) account_status
FROM spend_daily_level s

additional index on individual columns date account_id could be helpful