我有一个查询,其中我为“女性”和“男性”创建了两个列
SELECT
ID,
UserName,
concat(FirstName,' ',LastName) as Name,
iif(substring(ID,10,1) % 2 =0,'Female', 'Male') as Gender
INTO NewUsers1
FROM Users现在,我想知道男女的平均年龄。我想把它分成两栏,分别是“性别”和“平均年龄”,一行代表“女性平均年龄”,一行代表“男性平均年龄”。
我该怎么做?
从列ID中可以看出年龄,其中的数字是出生日期。
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

发布于 2021-03-17 13:56:08
这使您在不丢失其他列的情况下,在两列中获得平均年龄:
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发布于 2021-03-17 12:44:59
您可以使用条件聚合:
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;https://stackoverflow.com/questions/66673302
复制相似问题