How to create a view with a grouping of adjacent strings?

All good.

There is a need to group adjacent rows with the same values in certain fields.

For simplicity, from here such tables (on the left) need to get a (right):
| id | num | | num | count |
|----|-----| |-----|-------|
| 1 | 1 | | 1 | 2 |
| 2 | 1 | | 2 | 1 |
| 3 | 2 | | 1 | 3 |
| 4 | 1 | | 3 | 2 |
| 5 | 1 | | 4 | 1 |
| 6 | 1 | 
| 7 | 3 | 
| 8 | 3 | 
| 9 | 4 |


That said, it is thought, pogolino — made: sqlfiddle.com/#!9/c9c30b/1/0.

SELECT `num`, COUNT(`group`) AS `count` 
FROM(
 SELECT 
 `id`, `num`,
 IF (@prev = `num`, @group, @group := @group + 1) AS `group`,
 @prev := `num` AS `prev`
 FROM `test`
 JOIN (SELECT @prev := 0, @group := 0) AS `t1`
) AS `t2`
GROUP BY `group`;


But now there is a need to put this query in a view (VIEW), and this raises two problems:
1. In views you cannot use subqueries inside the FROM clause. You can work around this by creating an additional view for the subquery.
2. In views you cannot use variables. And this is the problem. To think of solutions to this problem without using variables I failed. Therefore we appeal to the community, I would be grateful for any help.

To abandon the idea it would not be desirable, because the actual query is very big (with lots of JOIN, UNION, etc). And work with it from code you need from one table, i.e., to have the opportunity to sort, filter, group, Limitati, etc.
July 9th 19 at 10:45
2 answers
July 9th 19 at 10:47
Procedure? :)
The procedure will not give those opportunities for work with a score that gives an indication that in my case need. Or it will be procedure with 100500 parameters. - Adeline.Goldner commented on July 9th 19 at 10:50
See, a colleague. Restrictions you cannot miss. Conclusion? Or to change from mysql to something else, either:

Doing the procedure that you will return recordset. Toss it to the table. Further table work as would work with vyuha. - lauren.Prosac commented on July 9th 19 at 10:53
July 9th 19 at 10:49
And on postgresql it is possible to rewrite?

Find more questions by tags DatabasesWeb DevelopmentSQLMySQL