mariadb-toolbox's People
mariadb-toolbox's Issues
MySQL 5.7 nested variable question, please help
RESET QUERY CACHE;
SELECT vip.user_id id
FROM (SELECT oo.user_id, IF(SUM(IF(oo.Rev >= 40, 1, 0)) >= 3, 1, 0) SSVIP_Seg,
IF(SUM(IF(oo.Rev < 40 AND oo.Rev >= 15, 1, 0)) >= 3, 1, 0) SVIP_Seg
FROM (SELECT kk.id, kk.user_id, kk.Rev, kk.created_at, kk.rn, IF(@prev1 <> kk.user_id, @RN1:=0, @RN1), @prev1:=kk.user_id, @RN1:=@RN1+1 AS rn1
FROM (SELECT pp.id, pp.user_id, pp.Rev, pp.created_at, pp.rn
FROM (SELECT up.id, up.user_id, created_at, ROUND(amountpayout_foreign_exchange_rate) Rev, IF(@Prev <> user_id, @rn:=0, @rn), @Prev:=user_id, @rn:=@rn+1 AS rn
FROM (SELECT id, user_id, amount, payout_foreign_exchange_rate, created_at
FROM XXXYYY.user_purchase
WHERE status = 'completed'
AND created_at >= NOW() -INTERVAL 6086400 SECOND
AND user_id IS NOT NULL
AND user_id > 0
ORDER BY user_id, id) up, (SELECT @rn:=0) rn, (SELECT @Prev:='') prev) pp
JOIN (SELECT pp.user_id, MAX(pp.rn) Max_rn
FROM (SELECT up.id, up.user_id, IF(@Prev <> user_id, @rn:=0, @rn), @Prev:=user_id, @rn:=@rn+1 AS rn
FROM (SELECT id, user_id
FROM XXXYYY.user_purchase
WHERE status = 'completed'
AND created_at >= NOW() -INTERVAL 6086400 SECOND
AND user_id IS NOT NULL
AND user_id > 0
ORDER BY user_id, id) up, (SELECT @rn:=0) rn, (SELECT @Prev:='') prev) pp
GROUP BY pp.user_id) jk
ON pp.user_id = jk.user_id AND pp.rn >= jk.Max_rn - 6 AND jk.Max_rn - 6 >= 1 / <2>WHERE pp.user_id = 240XX24*/) kk, (SELECT @RN1:=0) rn1, (SELECT @prev1:='') prev1
WHERE user_id IN (/250XX24,/ 240XX24)
/* <1> Here you cant limit only 1 user, ???????????????????????? */
) oo
GROUP BY oo.user_id) vip
WHERE vip.SVIP_Seg = 0 AND vip.SSVIP_Seg = 0;
If you are doing the limit in <1> with one user, the order will be miss sorted as below:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
id, user_id, Rev, created_at, rn, IF(@prev1 <> kk.user_id, @RN1:=0, @RN1), @prev1:=kk.user_id,rn1
29225 | 240XX24 | XX.0 | 2022-11-08 13:43:32.0 | 2.0 | 0 | 2405724 | 1.0
29321 | 240XX24 | XX.0 | 2022-11-10 20:40:49.0 | 3.0 | 1 | 2405724 | 2.0
29930 | 240XX24 | XX.0 | 2022-11-24 05:03:30.0 | 4.0 | 2 | 2405724 | 3.0
30408 | 240XX24 | XX.0 | 2022-12-16 04:02:46.0 | 5.0 | 3 | 2405724 | 4.0
30460 | 240XX24 | XX.0 | 2022-12-18 06:29:23.0 | 6.0 | 4 | 2405724 | 5.0
30483 | 240XX24 | XX.0 | 2022-12-18 23:49:47.0 | 7.0 | 5 | 2405724 | 6.0
29176 | 240XX24 | XX.0 | 2022-11-07 02:38:24.0 | 1.0 | 6 | 2405724 | 7.0
^ ^
But you can do the limitation in <2>, I want to know why such thing happened, would you mind explain the machinasm behind it, I was read somewhere that Mariadb team also created MySQL many years ago.
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. ๐๐๐
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google โค๏ธ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.