主页 / dba / 问题 / 298104
Accepted
Pantea
Asked: 2021-08-17 05:18:20 +0800 CST

查询将每列值分为两类：[Common , Not_Common]

  • 772

我有一个具有以下结构和数据的表：

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

**) 我要做的是将col_value列中的每个值分为两类：[常见，不常见]

**)'Common'如果一个值出现在每个中，则考虑一个值col_name，因此apple很常见，因为它出现在 中col_name = first and col_name = second and col_name = third。对于banana. Orange并不常见，因为它只是出现在col_name = second.

所需的输出将是这样的：

    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

我为此写的查询是：

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)

我想知道是否有更好的方法来做到这一点。

提前致谢

sql-server query-performance

1 个回答

  1. Best Answer

    执行此操作的两种方法如下（下面的所有代码都可以在SQL Server 的小提琴中找到- 有计划 -在这里- 最后的性能分析：

    桌子：

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

    填充它：

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

    第一种方式：

    首先，我们想知道一个水果在整个表格中出现了多少次。

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

    结果：

    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

    您有多种方法可以找到水果出现的次数少于您定义的 cnt (3) 的最大值common- 所以我们一眼就能看出orange是uncommon。

    所以，我正在使用 CTE 来做到这一点：

    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);

    结果：

    col_name    col_value   cnt
   third       orange     1

    瞧！

    为了更接近您自己的原始（不工作 - 见小提琴）查询，您可以这样做（再次，在小提琴中）：

    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;

    结果相同。

    第二种方式：

    如果您正在运行没有窗口功能的古董（或最新版本的 MySQL :-)），您也可以这样做：

    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
);

    结果：

    col_value   cnt
   orange     1

    等等，再来一次！

    你在问题中问：

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

    因此，我在小提琴的底部添加了以下几行（在此处记录）：

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

    最后

    SET SHOWPLAN_ALL ON;

    从 db<>fiddle 获得非常细粒度的时序似乎是不可能的，但计划很有趣。

    窗口函数查询产生以下计划（23 行）：

    |--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.

    而“老式”的则产生了这个 11 行的计划：

    |--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.

    鉴于我们缺乏明确的时间安排 - 无论如何，用如此少量的数据进行测试或多或少毫无意义，我会敦促您针对您自己的表和硬件测试任何和所有建议的解决方案......但是，作为一项规则拇指，计划越长，它们越慢，并且窗口函数会产生开销！从这里：

    如您所见，与传统方法相比，窗口聚合对性能有很大影响。

    将来，在问这种性质的问题时，请您自己提供小提琴-它提供了单一的事实来源并消除了重复劳动-帮助我们为您提供帮助！:-)

    • 2

相关问题