utilizzando ORDER BY in SQL per blocchi di dati

Question

Voglio sapere come ordino i dati in una query SQL ma solo in determinati blocchi. Fornirò un esempio per renderlo più semplice.

---------------------------
| height  |  rank  | name  |
-----------------------------
| 172  |  8     |   Bob    |
-----------------------------
| 183  |  8     |   John   |
-----------------------------
| 185  |  2     |   Mitch  |
-----------------------------
| 179 |   2     |   Sarah  |
-----------------------------
| 154   |  8    |   Martha |
---------------------------
| 190   |  2    |   Tom    |
---------------------------

Nell'esempio sopra, voglio fare un ORDER BY altezza DESC, MA solo la persona più alta di ogni grado viene ordinata e tutti gli altri nello stesso grado sono proprio sotto quella persona ordinati per altezza ASC. Quindi il risultato finale che voglio è:

---------------------------
| height  |  rank  | name  |
---------------------------
| 190   |  2    |   Tom    |
-----------------------------
| 179 |   2     |   Sarah  |
-----------------------------
| 185  |  2     |   Mitch  |
-----------------------------
| 183  |  8     |   John   |
-----------------------------
| 154  |  8     |   Martha |
----------------------------
| 172   |  8    |   Bob   |
---------------------------

Quindi Tom è il più alto, quindi va in cima, e automaticamente tutti gli altri nel suo rango vanno sotto di lui ma hanno organizzato ASC. John è il più alto dei restanti, quindi lui e il suo gruppo vanno dopo. Qual è la query migliore che posso utilizzare per ottenere questo risultato?

Answer 1

Per prima cosa determina il campione di ciascunorank

with rank_max as (
  select rank, max(height) as rank_height
    from heights
   group by rank
),

Determina la classifica per ogni grado in base al campione

 rank_ranking as (
  select rank, 
         dense_rank() over (order by rank_height desc) as rank_rank
    from rank_max
)

Ricollegati a entrambi i CTE per ottenere l'ordine specificato. Lo rm.rank_height != h.heightsfrutta il fatto che falseviene prima truequando ordinato per mettere il campione al vertice di ogni rankraggruppamento.

select h.* 
  from heights h
       join rank_ranking r on r.rank = h.rank
       join rank_max rm on rm.rank = h.rank
 order by r.rank_rank, 
          rm.rank_height != h.height,
          h.height;

Come sottolineato da Gordon Linoff, questo può essere semplificato come segue utilizzando solo le funzioni della finestra:

select *
  from heights
 order by max(height) over (partition by rank) desc,
          max(height) over (partition by rank) != height,
          height;

Violino funzionante aggiornato.

Answer 2

Lo definirei come:

select t.*
from (select t.*,
             max(height) over (partition by rank) as max_height
      from t
     ) t
order by max_height,
         rank,
         (height = max_height)::int desc,   -- put the largest heights first
         height desc;

Answer 3

Prova a ordinare sia in ordine crescente che discendente, quindi avvolgilo in un'istruzione case per scegliere l'ordine decrescente se è il primo classificato, altrimenti utilizza l'ordine crescente (aggiungi 1 all'ordine crescente per evitare sovrapposizioni).

SELECT  a.*, CASE hgt_desc
                WHEN 1 THEN hgt_desc
                ELSE hgt_asc
             END AS new_rank 
FROM    (
        SELECT  *, 
            ROW_NUMBER() OVER (PARTITION BY rank ORDER BY height ASC) + 1 AS hgt_asc, 
            ROW_NUMBER() OVER (PARTITION BY rank ORDER BY height DESC) AS hgt_desc 
        FROM    table 
        ) AS a 
ORDER BY a.rank, new_rank 

Answer 4

È possibile utilizzare la funzione finestra, come ROW_NUMBER. È ROW_NUMBER()una funzione finestra che assegna un numero intero sequenziale a ciascuna riga all'interno della partizione di un set di risultati.

Stai ottenendo numeri per tutte le righe (all'interno di ogni rango e ordine per altezza crescente) e il valore dell'altezza massima per ogni rango. E per l'ordine corretto, basta sostituire il numero con il valore dell'altezza massima su 0, gli altri rimangono senza cambiare:

Se hai bisogno di una colonna da ordinare per:

    Select *, case when height=max_val then 0 else num end as order_column from
    (
    --get the max height's value and order by height asc within each rank
    Select *, max(height) over(partition by rank) max_val ,row_number () over(partition by rank order by height) num
    from Table
    ) X
Order by rank asc,order_column asc

O hai solo bisogno di ordinare le righe in un ordine specifico :

Select * from
(
--get the max height's value and order by height asc within each rank
Select *, max(height) over(partition by rank) max_val ,row_number () over(partition by rank order by height) num
from Table
) X
Order by rank asc, 
Case (when height=max_val then 0 else num end ) asc