Bad indentation with window functions that use both PARTITION BY and ORDER BY

[matthewgdv]

I'll leave this example here which should illustrate the problem:

import sqlparse

stmt = """
SELECT  max(sub.game_name)                                                AS name
,       max(sub.game_desc)                                                AS desc
,       max(CASE WHEN sub.rank_asc = 1 THEN sub.player_name END)          AS winner
,       max(CASE WHEN sub.rank_desc = 1 THEN sub.player_name END)         AS loser
,       max(sub.participation)                                            AS participation
,       CASE WHEN max(sub.participation) >= 6 THEN true ELSE false END    AS legal
,       string_agg(DISTINCT sub.player_name, '<br>')                      AS participants
FROM
(
    SELECT
        game.id                                                         AS game_id
    ,   game.name                                                       AS game_name
    ,   game.desc                                                       AS game_desc
    ,   player.name                                                     AS player_name
    ,   row_number() OVER (PARTITION BY game_id ORDER BY place ASC)     AS rank_asc
    ,   row_number() OVER (PARTITION BY game_id ORDER BY place DESC)    AS rank_desc
    ,   count(0) OVER (PARTITION BY game_id)                            AS participation
    FROM leaderboards_game AS game
      JOIN leaderboards_place AS place
        ON game.id = place.game_id
      JOIN leaderboards_player AS player
        ON place.player_id = player.id
) AS sub
GROUP BY sub.game_id
ORDER BY sub.game_id DESC
"""

print(sqlparse.format(stmt, reindent=True).strip())

returns:

SELECT max(sub.game_name) AS name,
       max(sub.game_desc) AS desc,
       max(CASE
               WHEN sub.rank_asc = 1 THEN sub.player_name
           END) AS winner,
       max(CASE
               WHEN sub.rank_desc = 1 THEN sub.player_name
           END) AS loser,
       max(sub.participation) AS participation,
       CASE
           WHEN max(sub.participation) >= 6 THEN true
           ELSE false
       END AS legal,
       string_agg(DISTINCT sub.player_name, '<br>') AS participants
FROM
  (SELECT game.id AS game_id ,
          game.name AS game_name ,
          game.desc AS game_desc ,
          player.name AS player_name ,
          row_number() OVER (PARTITION BY game_id
                             ORDER BY place ASC) AS rank_asc ,
                            row_number() OVER (PARTITION BY game_id
                                               ORDER BY place DESC) AS rank_desc ,
                                              count(0) OVER (PARTITION BY game_id) AS participation
   FROM leaderboards_game AS game
   JOIN leaderboards_place AS place ON game.id = place.game_id
   JOIN leaderboards_player AS player ON place.player_id = player.id) AS sub
GROUP BY sub.game_id
ORDER BY sub.game_id DESC

Notice how it reindents after PARTITION BY, splitting out a single statement that really should be on one line and then breaking the indentation level for all remaining items in the SELECT clause. The more window functions like this the more levels of indentation you acquire.

Another thing I'd like to mention is it would be nice to also have the option to have CASE statements remain on a single line, but that's a separate issue, and one that someone else has already raised:

https://github.com/andialbrecht/sqlparse/issues/299

[surelili]

SELECT max(sub.game_name) AS name, max(sub.game_desc) AS DESC, max(CASE WHEN sub.rank_asc = 1 THEN sub.player_name END) AS winner, max(CASE WHEN sub.rank_desc = 1 THEN sub.player_name END) AS loser, max(sub.participation) AS participation, CASE WHEN max(sub.participation) >= 6 THEN TRUE ELSE FALSE END AS legal, string_agg(DISTINCT sub.player_name, '
') AS participants FROM (SELECT game.id AS game_id , game.name AS game_name , game.desc AS game_desc , player.name AS player_name , row_number() OVER (PARTITION BY game_id ORDER BY place ASC) AS rank_asc , row_number() OVER (PARTITION BY game_id ORDER BY place DESC) AS rank_desc , count(0) OVER (PARTITION BY game_id) AS participation FROM leaderboards_game AS game JOIN leaderboards_place AS place ON game.id = place.game_id JOIN leaderboards_player AS player ON place.player_id = player.id) AS sub GROUP BY sub.game_id ORDER BY sub.game_id DESC

进程已结束，退出代码为 0