如何在不使用任何函数或过程的情况下为每个用户运行此查询?

huangapple go评论62阅读模式
英文:

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 &gt; 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 &gt; 0) AS points
    FROM bet b
    WHERE game_date &lt; 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 &gt; 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 &gt; 0) AS points
    FROM bet b
    WHERE game_date &lt; 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) &gt; 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 &gt; 0
	JOIN (SELECT @sum:=0, @prev_user:=0) vars
	WHERE b.game_date &lt; 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 &gt; 0
        WHERE b.game_date &lt; 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) &gt; 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 &gt; 0
	JOIN (SELECT @sum:=0, @prev_user:=0) vars
	WHERE b.game_date &lt; 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 &gt; 0
        WHERE b.game_date &lt; NOW()
    ) t1
) t2
GROUP BY user_id;

huangapple
  • 本文由 发表于 2023年2月9日 03:10:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/75390630.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定