英文:
How can i run this query for each user without using any function or procedure?
问题
以下是翻译好的代码部分:
这是我的查询,用于返回特定用户=27的连胜记录:
SELECT MAX(sum) AS streak
FROM (
SELECT
game_date,
IF(points > 0, @sum:=@sum+1, @sum:=0) AS sum
FROM (
SELECT
game_date,
(SELECT COUNT(*) FROM point WHERE user_id = 27 AND bet_id = b.id AND goals > 0) AS points
FROM bet b
WHERE game_date < NOW()
ORDER BY game_date DESC
) t1, (SELECT @sum:=0) t2
) t;
**我尝试的内容:** 在我的本地 MySQL 上成功运行,但在 live phpMyAdmin 上出现错误,指出 user_id 是 where 子句中的未知列。
SELECT DISTINCT user_id,(
SELECT MAX(SUM) AS streak
FROM (
SELECT
game_date,
IF(points > 0, @sum:=@sum+1, @sum:=0) AS SUM
FROM (
SELECT
game_date,
(SELECT COUNT(*) FROM POINT WHERE user_id = p.user_id AND bet_id = b.id AND goals > 0) AS points
FROM bet b
WHERE game_date < NOW()
ORDER BY game_date DESC
) t1, (SELECT @sum:=0) t2
) t) AS streak FROM POINT p;
请注意,代码部分已被翻译,其余文本未被翻译。
英文:
Here is my query which returns streaks for specific user=27:
SELECT MAX(sum) AS streak
FROM (
SELECT
game_date,
IF(points > 0, @sum:=@sum+1, @sum:=0) AS sum
FROM (
SELECT
game_date,
(SELECT COUNT(*) FROM point WHERE user_id = 27 AND bet_id = b.id AND goals > 0) AS points
FROM bet b
WHERE game_date < NOW()
ORDER BY game_date DESC
) t1, (SELECT @sum:=0) t2
) t;
What i try: it runs successfully on my local MySQL but gives an error on live phpMyAdmin that user_id is an unknown column in the where clause.
SELECT DISTINCT user_id,(
SELECT MAX(SUM) AS streak
FROM (
SELECT
game_date,
IF(points > 0, @sum:=@sum+1, @sum:=0) AS SUM
FROM (
SELECT
game_date,
(SELECT COUNT(*) FROM POINT WHERE user_id = p.user_id AND bet_id = b.id AND goals > 0) AS points
FROM bet b
WHERE game_date < NOW()
ORDER BY game_date DESC
) t1, (SELECT @sum:=0) t2
) t) AS streak FROM POINT p;
答案1
得分: 1
以下是代码部分的翻译:
SELECT user_id, MAX(SUM) AS streak
FROM (
SELECT b.game_date, u.user_id,
IF(
IFNULL(p.bet_id, 0) > 0,
IF(@prev_user = u.user_id, @sum := @sum + 1, @sum := 1),
@sum := 0
) AS SUM,
@prev_user := u.user_id
FROM bet b
CROSS JOIN (SELECT DISTINCT user_id FROM `POINT`) u
LEFT JOIN `POINT` p ON b.id = p.bet_id AND u.user_id = p.user_id AND p.goals > 0
JOIN (SELECT @sum:=0, @prev_user:=0) vars
WHERE b.game_date < NOW()
ORDER BY u.user_id ASC, b.game_date DESC
) t
GROUP BY user_id;
SELECT user_id, IFNULL(MAX(streak), 0)
FROM (
SELECT *, rn1 - rn2,
IF (
goals IS NOT NULL,
ROW_NUMBER() OVER (PARTITION BY user_id, rn1 - rn2 ORDER BY game_date),
NULL
) AS streak
FROM (
SELECT b.id AS bet_id, b.game_date, u.user_id, p.goals,
ROW_NUMBER() OVER (ORDER BY u.user_id, game_date) rn1,
ROW_NUMBER() OVER (PARTITION BY u.user_id, IF(p.bet_id IS NULL, 0, 1) ORDER BY game_date) rn2
FROM bet b
CROSS JOIN (SELECT DISTINCT user_id FROM `POINT`) u
LEFT JOIN `POINT` p ON b.id = p.bet_id AND u.user_id = p.user_id AND p.goals > 0
WHERE b.game_date < NOW()
) t1
) t2
GROUP BY user_id;
英文:
Without any sample data to test against, I may be way off the mark but give this a try:
SELECT user_id, MAX(SUM) AS streak
FROM (
SELECT b.game_date, u.user_id,
IF(
IFNULL(p.bet_id, 0) > 0,
IF(@prev_user = u.user_id, @sum := @sum + 1, @sum := 1),
@sum := 0
) AS SUM,
@prev_user := u.user_id
FROM bet b
CROSS JOIN (SELECT DISTINCT user_id FROM `POINT`) u
LEFT JOIN `POINT` p ON b.id = p.bet_id AND u.user_id = p.user_id AND p.goals > 0
JOIN (SELECT @sum:=0, @prev_user:=0) vars
WHERE b.game_date < NOW()
ORDER BY u.user_id ASC, b.game_date DESC
) t
GROUP BY user_id;
This is a variation of the gaps-and-islands problem.This query takes a different ("modern") approach, using the ROW_NUMBER() window function -
SELECT user_id, IFNULL(MAX(streak), 0)
FROM (
SELECT *, rn1 - rn2,
IF (
goals IS NOT NULL,
ROW_NUMBER() OVER (PARTITION BY user_id, rn1 - rn2 ORDER BY game_date),
NULL
) AS streak
FROM (
SELECT b.id AS bet_id, b.game_date, u.user_id, p.goals,
ROW_NUMBER() OVER (ORDER BY u.user_id, game_date) rn1,
ROW_NUMBER() OVER (PARTITION BY u.user_id, IF(p.bet_id IS NULL, 0, 1) ORDER BY game_date) rn2
FROM bet b
CROSS JOIN (SELECT DISTINCT user_id FROM `POINT`) u
LEFT JOIN `POINT` p ON b.id = p.bet_id AND u.user_id = p.user_id AND p.goals > 0
WHERE b.game_date < NOW()
) t1
) t2
GROUP BY user_id;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论