Средний возраст SQL server в двух разных строках

#sql #sql-server

Вопрос:

У меня есть этот запрос, в котором я создал два столбца для «женский» и «мужской»

 SELECT
      ID,
      UserName,
      concat(FirstName,'  ',LastName) as Name,
      iif(substring(ID,10,1) % 2 =0,'Female', 'Male') as Gender
INTO NewUsers1
FROM   Users

Теперь я хочу получить средний возраст для обоих полов. Я хочу разделить это на два столбца с именами «пол» и «средний возраст» с одной строкой для «среднего возраста женщин» и одной строкой для «среднего возраста мужчин»

Как я могу это сделать?

Возраст виден из идентификатора столбца, где число — это дата рождения.

 ID 

500603-4268    <-- birth year is 3rd June 1950.
500607-6521    <-- birth year is 7th June 1950.
530407-7989    <-- birth year is 4th April 1953.
530720-7675
540430-4887

Ответ №1:

Это дает вам средний возраст в двух столбцах без потери других столбцов:

 select
    *
,   avg(case when Gender = 'Male' then null else age end)   over(partition by Gender)   avgAge_Female
,   avg(case when Gender = 'Female' then null else age end) over(partition by Gender)       avgAge_Male
from
    (
    select
        f1.*
        ,    datediff(
            year
            ,   cast(
                concat(
                    concat('19', left(f1.vl, 2)) 
                    , '-'
                    ,   right(left(f1.vl, 4), 2) 
                    , '-'
                    ,    right(f1.vl, 2) 
                ) 
            as date)  
            ,   getdate()
            ) age
    from
        (
        select
            ID,
            UserName,
            concat(FirstName,'  ',LastName) as Name,
            left(ID, charindex('-', ID) - 1) vl,
            iif(substring(ID,10,1) % 2 =0,'Female', 'Male') as Gender
        from Users
        ) f1
    ) g1

Редактировать: объединено в версию одного столбца:

Примечание: я использовал подзапросы, чтобы упростить понимание, поэтому это не оптимизированный запрос.

 select
    h1.ID
,   h1.UserName
,   h1.Name
,   h1.Gender
,   isnull(h1.avgAge_Male, h1.avgAge_Female) avgAge_MaleOrFemale
    from
        (
        select
                *
            ,   avg(case when Gender = 'Male' then null else age end)   over(partition by Gender)   avgAge_Female
            ,   avg(case when Gender = 'Female' then null else age end) over(partition by Gender)       avgAge_Male
            from
                (
                select
                    f1.*
                    ,    datediff(
                        year
                        ,   cast(
                            concat(
                                concat('19', left(f1.vl, 2)) 
                                , '-'
                                ,   right(left(f1.vl, 4), 2) 
                                , '-'
                                ,    right(f1.vl, 2) 
                            ) 
                        as date)  
                        ,   getdate()
                        ) age
                from
                    (
                    select
                        ID,
                        UserName,
                        concat(FirstName,'  ',LastName) as Name,
                        left(ID, charindex('-', ID) - 1) vl,
                        iif(substring(ID,10,1) % 2 =0,'Female', 'Male') as Gender
                    from Users
                    ) f1
                ) g1
        ) h1

1. Предпочтительным является один столбец «средний возраст» с одной строкой для каждого пола

2. Пожалуйста, проверьте последнюю версию. Я отредактировал ответ.

3. Очень признателен. Последний вопрос заключается в том, как скрыть ‘vl’, ‘avgAge_Female’ и ‘avgAge_Male’ столбцов… Похоже, что ADD HIDDEN в этом случае не работает

4. Вы можете просто обновить часть выбора, написав нужные столбцы вместо *. Я отредактировал ответ, вы можете проверить.

5. Да, понял. Большое спасибо за вашу помощь! Приветствия

Ответ №2:

Вы можете использовать условную агрегацию:

 SELECT AVG(CASE WHEN substring(ID, 10, 1) % 2 = 0 THEN AGE END) as AVG_AGE_FEMALE,
       AVG(CASE WHEN substring(ID, 10, 1) % 2 = 1 THEN AGE END) as AVG_AGE_MALE
FROM Users;