Início / dba / Perguntas / 298104
Accepted
Pantea
Asked: 2021-08-17 05:18:20 +0800 CST

Consulta para dividir cada valor de coluna em duas categorias: [Common , Not_Common]

  • 772

Eu tenho uma tabela com estrutura e dados abaixo:

create table PTEST
(
  col_name  VARCHAR(50),
  col_value VARCHAR(50)
)

    COL_NAME    COL_VALUE
   -----------------------
     first       apple
     first       banana
     second      apple
     second      banana
     second      orange
     third       apple
     third       banana

**) o que eu quero fazer é dividir cada valor na col_valuecoluna em duas categorias: [Comum,Not common]

**) Um valor é considerado 'Common'se for exibido para cada col_name, Então appleé comum, pois é exibido para col_name = first and col_name = second and col_name = third. O mesmo vale para banana. Orangenão é comum, pois é apenas apareceu para col_name = second.

A saída desejada seria assim:

    COL_NAME   COL_VALUE   STATUS
   ---------------------------------
    first       apple       Common
    first       banana      Common
    second      banana      Common
    second      apple       Common
    second      orange      Not common
    third       apple       Common
    third       banana      Common

A consulta que escrevi para isso é:

select col_name,
       col_value,
       case
         when count_col = count_val then
          'Common'
         else
          'Not common'
       end STATUS
  from (select t.col_name,
               count(distinct t.col_name) over() count_col,
               t.col_value,
               count(t.col_value) over(partition by t.col_value) count_val
          from PTEST t)

Eu queria saber se existem maneiras melhores de fazer isso.

desde já, obrigado

sql-server query-performance

1 respostas

  1. Best Answer

    Duas maneiras de fazer isso seriam as seguintes (todo o código abaixo está disponível no violino para SQL Server - com planos - aqui - análise de desempenho no final:

    Mesa:

    CREATE TABLE ptest
(
  col_name  VARCHAR (50) NOT NULL,
  col_value VARCHAR (50) NOT NULL,
  
  CONSTRAINT name_value_uq UNIQUE (col_name, col_value)
);

    Preencha-o:

    INSERT INTO ptest VALUES
('first',  'apple'),
('first',  'banana'),
('second', 'apple'),
('second', 'banana'),
('third',  'apple'),
('third',  'banana'),
('third',  'orange');

    Primeira forma:

    Primeiro, queremos descobrir quantas vezes uma fruta aparece na tabela geral.

    SELECT 
  col_name,
  col_value,
  COUNT(col_value) OVER (PARTITION BY col_value) AS cnt
FROM
  ptest;

    Resultado:

    col_name    col_value   cnt 
   first        apple     3 
   first       banana     3 
  second        apple     3 
  second       banana     3 
   third        apple     3 
   third       banana     3 
   third       orange     1 
7 rows

    Você tem várias maneiras de encontrar o número de vezes que uma fruta apareceu menor que o máximo de cnt (3) que é sua definição de common- para que possamos ver de relance que orangeé uncommon.

    Então, estou usando CTEs para fazer isso:

    WITH cte1 AS
(
  SELECT 
    col_name,
    col_value,
    COUNT(col_value) OVER (PARTITION BY col_value) AS cnt
    -- COUNT(col_value) OVER (PARTITION BY col_name ORDER BY col_value)
  FROM
    ptest
),
cte2 AS
(
  SELECT MAX (cnt) AS mcnt FROM cte1
)
SELECT * FROM cte1 WHERE cnt < (SELECT mcnt FROM cte2);

    Resultado:

    col_name    col_value   cnt
   third       orange     1

    E voilà!

    Para obter algo mais próximo da sua consulta original (não está funcionando - veja o violino), você pode fazer isso (novamente, no violino):

    WITH cte1 AS
(
  SELECT 
    col_name,
    col_value,
    COUNT(col_value) OVER (PARTITION BY col_value) AS cnt
    -- COUNT(col_value) OVER (PARTITION BY col_name ORDER BY col_value)
  FROM
    ptest
),
cte2 AS
(
  SELECT MAX (cnt) AS mcnt FROM cte1
)
SELECT 
  col_name,
  col_value,
  CASE
    WHEN cnt < (SELECT mcnt FROM cte2) THEN 'Uncommon'
    ELSE 'Common'
  END AS status
FROM cte1;

    Mesmo resultado.

    Segunda maneira:

    Você também pode fazer isso se estiver executando uma versão antiga (ou versões recentes do MySQL :-) ) que não possui funções de janela da seguinte forma:

    SELECT * FROM
(
  SELECT
    col_value, COUNT(col_value) AS cnt
  FROM 
    ptest
  GROUP BY col_value
) AS t
WHERE cnt < 
(
  SELECT MAX(cnt) FROM 
  (
    SELECT
      col_value, COUNT(col_value) AS cnt
    FROM 
      ptest
    GROUP BY col_value
  ) AS u
);

    Resultado:

    col_value   cnt
   orange     1

    E voilà bis!!

    Você pergunta na pergunta:

    I was wondering if there are better ways to do that.

    Então, adicionei à parte inferior do violino as seguintes linhas (documentadas aqui ):

    SET STATISTICS PROFILE ON;  
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

    e finalmente

    SET SHOWPLAN_ALL ON;

    Parece impossível obter temporizações muito refinadas do db<>fiddle, mas os planos são interessantes.

    A consulta da função da janela produz o seguinte plano (23 linhas):

    |--Nested Loops(Inner Join, WHERE:([Expr1003]<[Expr1008]))  1   2   1   Nested Loops    Inner Join  WHERE:([Expr1003]<[Expr1008])       7   0   2.926E-05   47  0.02971825  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value], [Expr1003]     PLAN_ROW    False   1
           |--Stream Aggregate(DEFINE:([Expr1008]=MAX([Expr1007]))) 1   3   2   Stream Aggregate    Aggregate       [Expr1008]=MAX([Expr1007])  1   0   4.7E-06 11  0.01484579  [Expr1008]      PLAN_ROW    False   1
           |    |--Nested Loops(Inner Join) 1   4   3   Nested Loops    Inner Join          7   0   0.0001227688    11  0.01484109  [Expr1007]      PLAN_ROW    False   1
           |         |--Table Spool 1   5   4   Table Spool Lazy Spool          3   0   0   36  0.01471354  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
           |         |    |--Segment    1   6   5   Segment Segment [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     7   0   1.5944E-05  36  0.0146976   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value], [Segment1011]      PLAN_ROW    False   1
           |         |         |--Sort(ORDER BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] ASC))  1   7   6   Sort    Sort    ORDER BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] ASC)      7   0.01126126  0.0001306923    36  0.01468165  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
           |         |              |--Index Scan(OBJECT:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[name_value_uq])) 1   8   7   Index Scan  Index Scan  OBJECT:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[name_value_uq])    [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] 7   0.003125    0.0001647   36  0.0032897   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
           |         |--Nested Loops(Inner Join, WHERE:((1)))   1   9   4   Nested Loops    Inner Join  WHERE:((1))     2.333333    0   1.5944E-06  36  3.1888E-06  [Expr1007]      PLAN_ROW    False   4
           |              |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0))) 1   10  9   Compute Scalar  Compute Scalar  DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0))  [Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0)   1   0   1.5944E-07  36  1.75384E-06 [Expr1007], [Expr1007]      PLAN_ROW    False   4
           |              |    |--Stream Aggregate(DEFINE:([Expr1012]=Count(*)))    1   11  10  Stream Aggregate    Aggregate       [Expr1012]=Count(*) 1   0   1.5944E-06  36  1.5944E-06  [Expr1012]      PLAN_ROW    False   4
           |              |         |--Table Spool  1   12  11  Table Spool Lazy Spool          2.333333    0   0   36  0   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   4
           |              |--Table Spool    1   13  9   Table Spool Lazy Spool          2.333333    0   0   36  0   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   4
           |--Nested Loops(Inner Join)  1   14  2   Nested Loops    Inner Join          7   0   0.0001227688    47  0.0148411   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value], [Expr1003]     PLAN_ROW    False   1
                |--Table Spool  1   15  14  Table Spool Lazy Spool          3   0   0   43  0.01471355  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
                |    |--Segment 1   16  15  Segment Segment [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     7   0   1.5944E-05  43  0.0146976   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value], [Segment1013]      PLAN_ROW    False   1
                |         |--Sort(ORDER BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] ASC))   1   17  16  Sort    Sort    ORDER BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] ASC)      7   0.01126126  0.0001306993    43  0.01468166  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
                |              |--Index Scan(OBJECT:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[name_value_uq]))  1   18  17  Index Scan  Index Scan  OBJECT:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[name_value_uq])    [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] 7   0.003125    0.0001647   43  0.0032897   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
                |--Nested Loops(Inner Join, WHERE:((1)))    1   19  14  Nested Loops    Inner Join  WHERE:((1))     2.333333    0   1.5944E-06  43  3.1888E-06  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value], [Expr1003]     PLAN_ROW    False   4
                     |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1014],0)))  1   20  19  Compute Scalar  Compute Scalar  DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1014],0))  [Expr1003]=CONVERT_IMPLICIT(int,[Expr1014],0)   1   0   1.5944E-07  43  1.75384E-06 [Expr1003], [Expr1003]      PLAN_ROW    False   4
                     |    |--Stream Aggregate(DEFINE:([Expr1014]=Count(*))) 1   21  20  Stream Aggregate    Aggregate       [Expr1014]=Count(*) 1   0   1.5944E-06  43  1.5944E-06  [Expr1014]      PLAN_ROW    False   4
                     |         |--Table Spool   1   22  21  Table Spool Lazy Spool          2.333333    0   0   43  0   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   4
                     |--Table Spool 1   23  19  Table Spool Lazy Spool          2.333333    0   0   43  0   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_name], [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   4
    23 rows
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.

    E o "antiquado" produz esse plano de 11 linhas:

    |--Nested Loops(Inner Join, WHERE:([Expr1003]<[Expr1008]))  1   2   1   Nested Loops    Inner Join  WHERE:([Expr1003]<[Expr1008])       3   0   1.254E-05   20  0.02939043  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value], [Expr1003]     PLAN_ROW    False   1
       |--Stream Aggregate(DEFINE:([Expr1008]=MAX([Expr1007]))) 1   3   2   Stream Aggregate    Aggregate       [Expr1008]=MAX([Expr1007])  1   0   2.3E-06 11  0.01468965  [Expr1008]      PLAN_ROW    False   1
       |    |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1015],0)))   1   4   3   Compute Scalar  Compute Scalar  DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1015],0))  [Expr1007]=CONVERT_IMPLICIT(int,[Expr1015],0)   3   0   0   11  0.01468735  [Expr1007]      PLAN_ROW    False   1
       |         |--Stream Aggregate(GROUP BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]) DEFINE:([Expr1015]=Count(*)))   1   5   4   Stream Aggregate    Aggregate   GROUP BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value])  [Expr1015]=Count(*) 3   0   5.7E-06 11  0.01468735  [Expr1015]      PLAN_ROW    False   1
       |              |--Sort(ORDER BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] ASC))   1   6   5   Sort    Sort    ORDER BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] ASC)      7   0.01126126  0.0001306923    36  0.01468165  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
       |                   |--Index Scan(OBJECT:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[name_value_uq]))  1   7   6   Index Scan  Index Scan  OBJECT:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[name_value_uq])    [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] 7   0.003125    0.0001647   36  0.0032897   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
       |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1016],0)))    1   8   2   Compute Scalar  Compute Scalar  DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1016],0))  [Expr1003]=CONVERT_IMPLICIT(int,[Expr1016],0)   3   0   0   20  0.01468733  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value], [Expr1003]     PLAN_ROW    False   1
            |--Stream Aggregate(GROUP BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]) DEFINE:([Expr1016]=Count(*)))    1   9   8   Stream Aggregate    Aggregate   GROUP BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value])  [Expr1016]=Count(*) 3   0   5.7E-06 20  0.01468733  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value], [Expr1016]     PLAN_ROW    False   1
                 |--Sort(ORDER BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] ASC))    1   10  9   Sort    Sort    ORDER BY:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] ASC)      7   0.01126126  0.0001306723    16  0.01468163  [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
                      |--Index Scan(OBJECT:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[name_value_uq]))   1   11  10  Index Scan  Index Scan  OBJECT:([fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[name_value_uq])    [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value] 7   0.003125    0.0001647   16  0.0032897   [fiddle_404696dc8d6846e389dd3d04d0ec3512].[dbo].[ptest].[col_value]     PLAN_ROW    False   1
11 rows
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

    Dado que não temos timings explícitos - e de qualquer forma, testar com uma quantidade tão pequena de dados é mais ou menos sem sentido, recomendo que você teste todas e quaisquer soluções propostas em suas próprias tabelas e hardware ... polegar, quanto mais longos os planos, mais lentos eles são e as funções da janela incorrem em uma sobrecarga! A partir daqui :

    Como você pode ver, há um grande impacto no desempenho com agregados de janela em relação aos métodos tradicionais.

    No futuro, ao fazer perguntas dessa natureza, você poderia fornecer o violino - ele fornece uma única fonte de verdade e elimina a duplicação de esforços - ajude-nos a ajudá-lo! :-)

    • 2

relate perguntas