Home>

■ Environment
MySQL version: 5.6.36

■ Question (※ 2019/11/21 00:00 We have revised it a lot. I'm sorry.)
The performance of "sample data", "expected results", and "SQL (expectable results can be acquired but takes time)" is slow.
If i know how to improve, can you tell me?

Sample data
Name, X, Y, create_time
(A, 0,0, '2019-11-20 01:00'),
(A, 0,1, '2019-11-20 01:10'),
(B, 0,1, '2019-11-20 01:20'),
(A, 0,1, '2019-11-20 01:30'),
(A, 1,1, '2019-11-20 01:40'),
(B, 1,1, '2019-11-20 01:50'),
(A, 0,1, '2019-11-20 02:00'),
(A, 0,1, '2019-11-20 03:50'),
(B, 0,1, '2019-11-20 05:50')
● Expected result… I want to know the number of occurrences of the name by pattern until then
| Name | X | Y | count | create_time | 0-0 | 0-1 | 1-0 | 1-1 |
| A | 0 | 0 | 2019-11-20 01: 00 | 0 | 0 | 0 | 0 |
| A | 0 | 1 | 2019-11-20 01: 10 | 1 | 0 | 0 | 0 |
| B | 0 | 1 | 2019-11-20 01: 20 | 0 | 0 | 0 | 0 |
| A | 0 | 1 | 2019-11-20 01: 30 | 1 | 1 | 0 | 0 |
| A | 1 | 1 | 2019-11-20 01: 40 | 1 | 2 | 0 | 0 |
| B | 1 | 1 | 2019-11-20 01: 50 | 0 | 1 | 0 | 0 |
| A | 0 | 1 | 2019-11-20 02: 00 | 1 | 2 | 0 | 1 |
| A | 0 | 1 | 2019-11-20 03: 50 | 1 | 3 | 0 | 1 |
| B | 0 | 1 | 2019-11-20 05: 50 | 0 | 1 | 0 | 1 |
Select Name, X, Y, create_time, (
                            Select count (X = 0 and Y = 0)
                            From Table as B
                            Where A.Name = B.Name
                             And A.create_time>B.create_time
                           ),
                           (
                            Select count (X = 0 and Y = 1)
                            From Table as B
                            Where A.Name = B.Name
                             And A.create_time>B.create_time
                           )
                           (
                            Select count (X = 1 and Y = 0)
                            From Table as B
                            Where A.Name = B.Name
                             And A.create_time>B.create_time
                           ),
                           (
                            Select count (X = 1 and Y = 1)
                            From Table as B
                            Where A.Name = B.Name
                             And A.create_time>B.create_time
                           )
From Table as A
(* Execution/expected results can be acquired, but it takes time)


For each combination of X and Y, I want to count data that is older than the corresponding data (create_time).
Due to the large number of combinations and rows, the query never ends.

The MySQL version above doesn't seem to work with the With clause/window function ...
I tried to divide the subquery part with Case and try to execute it in one round of the table, but it did not go well.

If i have any missing or unclear points, please let me know.
Thanks for your cooperation.