主页 / coding / 问题 / 79317991
Accepted
Homer Jay Simpson
Asked: 2024-12-30 23:39:12 +0800 CST

SQL Server 中的表函数具有多个参数作为参数

  • 772

我在 SQL Server 2016 中有一个名为的表df：

-- Create a new table with department and gender columns
CREATE TABLE df 
(
    country VARCHAR(50),
    year INT,
    val1 INT,
    val2 INT,
    val3 INT,
    department VARCHAR(50),
    gender VARCHAR(10)
);

-- Insert data into the new table, including department and gender
INSERT INTO df (country, year, val1, val2, val3, department, gender) 
VALUES ('USA', 2020, 4, 4, 5, 'Sales', 'Male'),
('USA', 2020, 4, 4, 5, 'Sales', 'Male'),
('USA', 2020, 5, 5, 5, 'Sales', 'Female'),
('USA', 2020, 5, 5, 5, 'Sales', 'Female'),
('USA', 2020, 1, 1, 5, 'Sales', 'Male'),
('USA', 2020, 3, 3, 5, 'Sales', 'Female'),
('USA', 2020, 4, 2, 5, 'Sales', 'Male'),
('USA', 2020, 1, 1, 5, 'Sales', 'Female'),
('USA', 2020, 2, 2, 5, 'Sales', 'Male'),
('Canada', 2020, 2, 2, 3, 'HR', 'Female'),
('Canada', 2020, 2, 2, 3, 'HR', 'Female'),
('Canada', 2020, 2, 2, 3, 'HR', 'Male'),
('Canada', 2020, 2, 2, 3, 'HR', 'Male'),
('Canada', 2020, 5, 5, 3, 'HR', 'Female'),
('Canada', 2020, 5, 5, 3, 'HR', 'Male'),
('Canada', 2020, 1, 1, 3, 'HR', 'Female'),
('Canada', 2020, 1, 1, 3, 'HR', 'Male'),
('Canada', 2020, 3, 4, 3, 'HR', 'Female'),
('Canada', 2020, 3, 4, 3, 'HR', 'Male'),
('Canada', 2020, 5, 4, 3, 'HR', 'Female'),
('Canada', 2020, 5, 4, 5, 'HR', 'Male'),
('Canada', 2020, 5, 4, 5, 'HR', 'Female'),
('Germany', 2022, 5, 5, 4, 'IT', 'Male'),
('France', 2020, 1, 1, 2, 'Finance', 'Female'),
('France', 2020, 1, 1, 2, 'Finance', 'Female'),
('France', 2020, 3, 2, 2, 'Finance', 'Male'),
('France', 2020, 3, 4, 2, 'Finance', 'Female'),
('France', 2020, 3, 5, 5, 'Finance', 'Male'),
('France', 2020, 3, 4, 4, 'Finance', 'Female'),
('France', 2020, 3, 4, 4, 'Finance', 'Male'),
('France', 2020, 3, 4, 3, 'Finance', 'Female'),
('UK', 2021, 4, 2, 3, 'Marketing', 'Male'),
('Australia', 2022, 3, 3, 4, 'Support', 'Female'),
('Italy', 2020, 5, 5, 5, 'Operations', 'Male'),
('Italy', 2020, 5, 5, 5, 'Operations', 'Female'),
('Italy', 2020, 5, 1, 1, 'Operations', 'Male'),
('Italy', 2020, 4, 4, 1, 'Operations', 'Female'),
('Italy', 2020, 2, 1, 2, 'Operations', 'Male'),
('Italy', 2020, 3, 5, 3, 'Operations', 'Female'),
('Spain', 2021, 1, 2, 3, 'Customer Service', 'Male'),
('Mexico', 2022, 4, 4, 4, 'Logistics', 'Female'),
('Brazil', 2020, 4, 1, 1, 'R&D', 'Male'),
('Brazil', 2020, 4, 1, 1, 'R&D', 'Female'),
('Brazil', 2020, 4, 3, 4, 'R&D', 'Male'),
('Brazil', 2020, 5, 3, 5, 'R&D', 'Female'),
('Brazil', 2020, 5, 3, 5, 'R&D', 'Male'),
('Brazil', 2020, 3, 3, 1, 'R&D', 'Female'),
('Brazil', 2020, 2, 3, 1, 'R&D', 'Male');

-- Select all rows from the new table to check the data
SELECT * FROM df;

通过此表，我根据一些过滤创建了一些百分比和计数列。

-- Parameters
DECLARE @Year INT = 2020;
DECLARE @Metric VARCHAR(50) = 'count'; 
DECLARE @Gender VARCHAR(20) = NULL; -- Set to specific gender (e.g., 'Male', 'Female') or NULL to include all
DECLARE @Department VARCHAR(50) = NULL; -- Set to specific department (e.g., 'HR', 'Engineering') or NULL to include all
-- Set @Metric to 'dissatisfaction', 'satisfaction', or 'count'

WITH UnpivotedData AS 
(
    SELECT country, gender, department, year, Vals
    FROM 
        (SELECT country, gender, department, year, val1, val2, val3
         FROM df) AS SourceTable
    UNPIVOT 
        (Vals FOR ValueColumn IN (val1, val2, val3)) AS Unpivoted
    WHERE year = @Year
),
Proportions AS 
(
    SELECT 
        country,
        gender,
        department,
        CASE 
            WHEN Vals = 1 THEN 'Very Dissatisfied'
            WHEN Vals = 2 THEN 'Dissatisfied'
            WHEN Vals = 3 THEN 'Neutral'
            WHEN Vals = 4 THEN 'Satisfied'
            WHEN Vals = 5 THEN 'Very Satisfied'
        END AS SatisfactionLevel,
        COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (PARTITION BY country, gender, department) AS Proportion
    FROM 
        UnpivotedData
    GROUP BY 
        country, gender, department, Vals
),
Pivoted AS 
(
    SELECT country, gender, department, 
           [Very Dissatisfied], 
           [Dissatisfied], 
           [Neutral], 
           [Satisfied], 
           [Very Satisfied]
    FROM Proportions
    PIVOT 
        (MAX(Proportion)
         FOR SatisfactionLevel IN ([Very Dissatisfied], [Dissatisfied], [Neutral], [Satisfied], [Very Satisfied])) AS p
),
CountryCounts AS 
(
    SELECT 
        CASE WHEN country IS NULL THEN 'Unknown' ELSE country END AS country,
        gender, 
        department,
        COUNT(*) AS Total
    FROM df
    WHERE year = @Year
    -- Apply filters for gender and department if provided
    AND (@Gender IS NULL OR gender = @Gender)
    AND (@Department IS NULL OR department = @Department)
    GROUP BY CASE WHEN country IS NULL THEN 'Unknown' ELSE country END, gender, department
),
OrderedData AS 
(
    SELECT 
        p.country,
        p.gender,
        p.department,
        [Very Dissatisfied],
        [Dissatisfied],
        [Neutral],
        [Satisfied],
        [Very Satisfied],
        c.Total,
        CASE 
            WHEN @Metric = 'satisfaction' THEN ISNULL([Satisfied], 0) + ISNULL([Very Satisfied], 0)
            WHEN @Metric = 'dissatisfaction' THEN ISNULL([Very Dissatisfied], 0) + ISNULL([Dissatisfied], 0)
            WHEN @Metric = 'count' THEN c.Total
        END AS SortValue
    FROM Pivoted AS p
    INNER JOIN CountryCounts AS c ON p.country = c.country AND p.gender = c.gender AND p.department = c.department
)
SELECT 
    country,
    gender,
    department,
    [Very Dissatisfied],
    [Dissatisfied],
    [Neutral],
    [Satisfied],
    [Very Satisfied],
    Total
FROM 
    OrderedData
ORDER BY 
    SortValue DESC;

我想创建一个具有 3 个参数的表函数：

  1. 公制
  2. 年
  3. 因素

Factor可以是性别或部门，或者两者皆可。例如，如果Factor是性别，则表格按性别分组；如果是部门，则表格按部门分组。

如果两者同时进行分组。如果Factor为空或默认，则根本不进行分组。

关于Year：如果Year传入的是按年份分组，如果Year是空，则显示所有年份，不进行分组。

有没有办法在 SQL Server 中做到这一点？

我这里有一把小提琴

sql-server

2 个回答

  1. Best Answer

    正如我在您之前的 SQL 问题中告诉您的那样，您把这个问题复杂化了。

    您可以在 CTE 的单个级别中执行过滤、取消透视和透视，并且只需要一个级别来添加Total，如果有一个列，这本身就没有必要ID，因为您可以执行COUNT(DISTINCT ID)。

    要创建函数，只需添加常规CREATE FUNCTION语法。您不能ORDER BY向表添加函数，它基本上只是一个视图。您需要将其添加到外部查询中。

    CREATE OR ALTER FUNCTION dbo.MyAggregation (
    @Year INT,
    @Gender VARCHAR(20), -- Set to specific gender (e.g., 'Male', 'Female') or NULL to include all
    @Department VARCHAR(50), -- Set to specific department (e.g., 'HR', 'Engineering') or NULL to include all
    @Metric VARCHAR(50) -- Set @Metric to 'dissatisfaction', 'satisfaction', or 'count'
)
RETURNS TABLE
AS RETURN

WITH AllRows AS (
      SELECT *,
          COUNT(*) OVER (PARTITION BY country, gender, department, year) AS Total
      FROM df
      WHERE (year = @Year OR @Year IS NULL)
        AND (Department = @Department OR @DepARTMENT IS NULL)
        AND (Gender = @Gender OR @Gender IS NULL)
)
SELECT
      country,
      gender,
      department,
      year,
      COUNT(CASE WHEN Vals = 1 THEN 1 END) * 1.0
         / SUM(COUNT(*)) OVER (PARTITION BY country, gender, department, year) AS [Very Dissatisfied],
      COUNT(CASE WHEN Vals = 2 THEN 1 END) * 1.0
         / SUM(COUNT(*)) OVER (PARTITION BY country, gender, department, year) AS [Dissatisfied],
      COUNT(CASE WHEN Vals = 3 THEN 1 END) * 1.0
         / SUM(COUNT(*)) OVER (PARTITION BY country, gender, department, year) AS [Neutral],
      COUNT(CASE WHEN Vals = 4 THEN 1 END) * 1.0
         / SUM(COUNT(*)) OVER (PARTITION BY country, gender, department, year) AS [Satisfied],
      COUNT(CASE WHEN Vals = 5 THEN 1 END) * 1.0
         / SUM(COUNT(*)) OVER (PARTITION BY country, gender, department, year) AS [Very Satisfied],
      MIN(Total) AS Total,
      CASE @Metric
        WHEN 'satisfaction' THEN
          COUNT(CASE WHEN Vals IN (4, 5) THEN 1 END) * 1.0
            / SUM(COUNT(*)) OVER (PARTITION BY country, gender, department, year)
        WHEN 'dissatisfaction' THEN
          COUNT(CASE WHEN Vals IN (1, 2) THEN 1 END) * 1.0
           / SUM(COUNT(*)) OVER (PARTITION BY country, gender, department, year)
        WHEN 'count' THEN MIN(Total)
      END AS SortValue
FROM AllRows
CROSS APPLY (VALUES
        ('val1', val1),
        ('val2', val2),
        ('val3', val3)
) v(ValueColumn, Vals)
GROUP BY
    country,
    gender,
    department,
    year;

    然后你就做

    SELECT *
FROM dbo.MyAggregation(2020, NULL, NULL, 'count')
ORDER BY
    SortValue;

    db<>小提琴

    请注意，sort-value 参数不应从变量或横向连接传入，因为这会大大降低查询速度。如果它是常量字符串，那么优化器可以将其排除在外。

    Adding in dynamic grouping substantially complicates this, because now you need to null out the values before you group them up (as shown in the other answer). It will also be really slow on large tables, as you can't use indexes. I would strongly recommend you create separate functions with different grouping/partitioning constructs, alternatively do this in dynamic SQL.

    • 1

  2. 我们正在对要分组的列应用值替换。例如，如果参数 @factorGender 为空，则我们按值分组，gender否则按常量值分组all- 事实上，不按性别分组。

    为了简化问题，子查询相当于你的 UNPIVOT-PIVOT 操作。我们可以直接计算 val1、val2、val3 的值分散度。表达式

    iif(val1=1,1.0,0)+iif(val2=1,1.0,0)+iif(val3=1,1.0,0) cv1

    或

    case  when val1=1 then 1.0 else 0 end
   +case when val2=1 then 1.0 else 0 end
   +case when val3=1 then 1.0 else 0 end as cv1

    对所有行计数 val1=1、val2=1、val3=1。

    查看示例

    更新 1。在 @DaleK 评论之后，我决定我确实需要更准确地回答这个问题，并提出一个函数示例和一个其使用示例。

    
CREATE FUNCTION SatisfiedByGroups (
 -- -- Parameters for row filters
    @Year INT = 2020
   ,@Metric VARCHAR(50) ='dissatisfaction' -- Set @Metric to 'satisfaction','dissatisfaction', 'count' ; 
   ,@Gender VARCHAR(20) = NULL  -- Set to specific gender (e.g., 'Male', 'Female') or NULL to include all
   ,@Department VARCHAR(50) = NULL  -- Set to specific department (e.g., 'HR', 'Engineering') or NULL to include all

-- parameters for grouping
   ,@factorYear int = null -- 0 ; -- Set to any value if do not group by, else null
   ,@factorgender VARCHAR(50) = null  --'all'  -- Set to any value if do not group by, else null
   ,@factorDepartment VARCHAR(50) = null --'all'  -- Set to value if do not group by, else null
   ,@factorCountry VARCHAR(50) = null -- 'all' -- Set to value if do not group by, else null
  )
RETURNS TABLE
AS
RETURN (
select grCountry country,grGender gender,grDepartment department
  ,sum(cv1)/count(*)/3.0 [Very Dissatisfied]
  ,sum(cv2)/count(*)/3.0 [Dissatisfied]
  ,sum(cv3)/count(*)/3.0 [Neutral]
  ,sum(cv4)/count(*)/3.0 [Satisfied]
  ,sum(cv5)/count(*)/3.0 [Very Satisfied]
  ,count(*) total
  ,CASE 
      WHEN @Metric = 'satisfaction' THEN (sum(cv4)+sum(cv5))/3.0/count(*)
      WHEN @Metric = 'dissatisfaction' THEN (sum(cv1)+sum(cv2))/3.0/count(*)
      WHEN @Metric = 'count' THEN count(*)
   END AS SortValue
  ,grCountry,grYear,grDepartment,grGender,@metric as metric
from(
select coalesce(country,'unknown') country,year ,gender,department
  ,iif(val1=1,1.0,0)+iif(val2=1,1.0,0)+iif(val3=1,1.0,0) cv1 
  ,iif(val1=2,1.0,0)+iif(val2=2,1.0,0)+iif(val3=2,1.0,0) cv2
  ,iif(val1=3,1.0,0)+iif(val2=3,1.0,0)+iif(val3=3,1.0,0) cv3
  ,iif(val1=4,1.0,0)+iif(val2=4,1.0,0)+iif(val3=4,1.0,0) cv4
  ,iif(val1=5,1.0,0)+iif(val2=5,1.0,0)+iif(val3=5,1.0,0) cv5
  ,coalesce(@factorYear,year) grYear
  ,coalesce(@FactorDepartment,department)grDepartment
  ,coalesce(@factorGender,gender) grGender
  ,coalesce(@factorCountry,coalesce(country,'UNKNOWN')) grCountry
from df
WHERE (year = @Year or @Year is null)
      -- Apply filters for gender and department if provided
    AND (@Gender IS NULL OR gender = @Gender)
    AND (@Department IS NULL OR department = @Department)
)d 
group by grCountry,grYear,grDepartment,grGender
);

    并调用此函数

    select * from SatisfiedByGroups(
        2020 -- @year
       ,'count' -- @metric
       ,NULL    -- @gender
       ,NULL    -- @department
       ,null -- @factorYear
       ,null -- @factorgender
       ,null -- @factorDepartment
       ,null -- @factorCountry
)

    输出为

    国家 性别 部门 非常不满意 不满 中性的 使满意 非常满意 全部的 排序值 国家 年份 所属部门 性别 度量
    加拿大 女性 人力资源 0.095238 0.190476 0.333333 0.142857 0.238095 7 7.000000 加拿大 2020 人力资源 女性 数数
    加拿大 男性 人力资源 0.111111 0.222222 0.333333 0.111111 0.222222 6 6.000000 加拿大 2020 人力资源 男性 数数
    法国 女性 金融 0.266666 0.200000 0.266666 0.266666 0.000000 5 5.000000 法国 2020 金融 女性 数数
    美国 男性 销售量 0.133333 0.200000 0.000000 0.333333 0.333333 5 5.000000 美国 2020 销售量 男性 数数
    美国 女性 销售量 0.166666 0.000000 0.166666 0.000000 0.666666 4 4.000000 美国 2020 销售量 女性 数数
    巴西 男性 研发 0.250000 0.083333 0.250000 0.250000 0.166666 4 4.000000 巴西 2020 研发 男性 数数
    巴西 女性 研发 0.333333 0.000000 0.333333 0.111111 0.222222 3 3.000000 巴西 2020 研发 女性 数数
    法国 男性 金融 0.000000 0.222222 0.333333 0.222222 0.222222 3 3.000000 法国 2020 金融 男性 数数
    意大利 女性 运营 0.111111 0.000000 0.222222 0.222222 0.444444 3 3.000000 意大利 2020 运营 女性 数数
    意大利 男性 运营 0.333333 0.222222 0.000000 0.000000 0.444444 3 3.000000 意大利 2020 运营 男性 数数

    因此，尝试使用另一组条件运行查询。

    select * from SatisfiedByGroups(
        2020 -- @year
       ,'dissatisfaction' -- @metric
       ,NULL    -- @gender
       ,NULL    -- @department
       ,null  -- @factorYear
       ,'all' -- @factorgender
       ,'all' -- @factorDepartment
       ,null -- @factorCountry
)
    国家 性别 部门 非常不满意 不满 中性的 使满意 非常满意 全部的 排序值 国家 年份 所属部门 性别 度量
    法国 全部 全部 0.166666 0.208333 0.291666 0.250000 0.083333 8 0.375000 法国 2020 全部 全部 不满
    意大利 全部 全部 0.222222 0.111111 0.111111 0.111111 0.444444 6 0.333333 意大利 2020 全部 全部 不满
    巴西 全部 全部 0.285714 0.047619 0.285714 0.190476 0.190476 7 0.333333 巴西 2020 全部 全部 不满
    加拿大 全部 全部 0.102564 0.205128 0.333333 0.128205 0.230769 十三 0.307692 加拿大 2020 全部 全部 不满
    美国 全部 全部 0.148148 0.111111 0.074074 0.185185 0.481481 9 0.259259 美国 2020 全部 全部 不满

    小提琴

    如果您的服务器上没有可用的函数，IIF(...) 请使用以下命令转换此表达式case when ... end

      ,case  when val1=1 then 1.0 else 0 end
   +case when val2=1 then 1.0 else 0 end
   +case when val3=1 then 1.0 else 0 end as cv1
  ,case  when val1=2 then 1.0 else 0 end
   +case when val2=2 then 1.0 else 0 end
   +case when val3=2 then 1.0 else 0 end as cv2
  ,case  when val1=3 then 1.0 else 0 end
   +case when val2=3 then 1.0 else 0 end
   +case when val3=3 then 1.0 else 0 end as cv3
  ,case  when val1=4 then 1.0 else 0 end
   +case when val2=4 then 1.0 else 0 end
   +case when val3=4 then 1.0 else 0 end as cv4
  ,case  when val1=5 then 1.0 else 0 end
   +case when val2=5 then 1.0 else 0 end
   +case when val3=5 then 1.0 else 0 end as cv5
--   ,iif(val1=1,1.0,0)+iif(val2=1,1.0,0)+iif(val3=1,1.0,0) cv1 
--   ,iif(val1=2,1.0,0)+iif(val2=2,1.0,0)+iif(val3=2,1.0,0) cv2
--  ,iif(val1=3,1.0,0)+iif(val2=3,1.0,0)+iif(val3=3,1.0,0) cv3
--  ,iif(val1=4,1.0,0)+iif(val2=4,1.0,0)+iif(val3=4,1.0,0) cv4
--  ,iif(val1=5,1.0,0)+iif(val2=5,1.0,0)+iif(val3=5,1.0,0) cv5

    小提琴
    和

    • -1

相关问题