使用Sqlite3(python)计算员工的平均年龄

a11xaf1n 于 2023-03-08 发布在 SQLite

关注(0)|答案(2)|浏览(173)

我需要从每个人的年龄中减去他们的工作年数，然后取平均值，我的最终答案应该是一个整数来代表被雇用时的平均年龄。
下面是我的代码

select HireDate,
CASE
    WHEN strftime('%m', date('now')) > strftime('%m', date(BirthDate))
    THEN strftime('%Y', date('now')) - strftime('%Y', date(BirthDate))
    
    WHEN strftime('%m', date('now')) = strftime('%m', date(BirthDate)) THEN
        CASE
            WHEN strftime('%d', date('now')) >= strftime('%d', date(BirthDate))
            THEN strftime('%Y', date('now')) - strftime('%Y', date(BirthDate))
            
            ELSE strftime('%Y', date('now')) - strftime('%Y', date(BirthDate)) -1
        END
        
    WHEN strftime('%m', date('now')) < strftime('%m', date(BirthDate)) THEN strftime('%Y', date('now')) - strftime('%Y', date(BirthDate)) -1
 
 END AS 'age',
CASE
    WHEN 1>0
    THEN (strftime('%Y', date(HireDate)) - strftime('%Y', date('now')))
END AS 'yw'
from Employee

这将显示员工的入职日期、年龄和工作年限：

有人知道为什么减去两个病例不起作用吗？

sqlite

来源：https://stackoverflow.com/questions/75630091/taking-the-average-age-an-employee-is-hired-with-sqlite3python

2条答案

按热度按时间

bn31dyow1#

试试看：-

WITH cte1 AS (
    SELECT HireDate,
    CASE
        WHEN strftime('%m', date('now')) > strftime('%m', date(BirthDate))
        THEN strftime('%Y', date('now')) - strftime('%Y', date(BirthDate))
        
        WHEN strftime('%m', date('now')) = strftime('%m', date(BirthDate)) THEN
            CASE
                WHEN strftime('%d', date('now')) >= strftime('%d', date(BirthDate))
                THEN strftime('%Y', date('now')) - strftime('%Y', date(BirthDate))
                
                ELSE strftime('%Y', date('now')) - strftime('%Y', date(BirthDate)) -1
            END
            
        WHEN strftime('%m', date('now')) < strftime('%m', date(BirthDate)) THEN strftime('%Y', date('now')) - strftime('%Y', date(BirthDate)) -1
     
     END AS age,
    CASE
        WHEN 1>0
        THEN (strftime('%Y', date(HireDate)) - strftime('%Y', date('now')))
    END AS yw
    from Employee
)
SELECT avg(age-yw) AS answer FROM cte1;

这克服了找不到age和yw列的问题。
下面是一个工作示例，它密切（足够）反映了数据：

DROP TABLE IF EXISTS employee;
CREATE TABLE IF NOT EXISTS employee (birthdate TEXT, hiredate TEXT);
INSERT INTO employee VALUES
    ('1980-06-01','2024-05-01')
    ,('1983-09-01','2024-08-14')
    ,('1995-09-01','2024-04-01')
    ,('1969-09-01','2025-05-03')
    ,('1995-09-01','2025-10-17')
    ,('1992-09-01','2026-01-02')
    ,('1989-09-01','2026-03-05')
    ,('1997-09-01','2026-11-15')
;
WITH cte1 AS (
    SELECT HireDate,
    CASE
        WHEN strftime('%m', date('now')) > strftime('%m', date(BirthDate))
        THEN strftime('%Y', date('now')) - strftime('%Y', date(BirthDate))
        
        WHEN strftime('%m', date('now')) = strftime('%m', date(BirthDate)) THEN
            CASE
                WHEN strftime('%d', date('now')) >= strftime('%d', date(BirthDate))
                THEN strftime('%Y', date('now')) - strftime('%Y', date(BirthDate))
                
                ELSE strftime('%Y', date('now')) - strftime('%Y', date(BirthDate)) -1
            END
            
        WHEN strftime('%m', date('now')) < strftime('%m', date(BirthDate)) THEN strftime('%Y', date('now')) - strftime('%Y', date(BirthDate)) -1
     
     END AS age,
    CASE
        WHEN 1>0
        THEN (strftime('%Y', date(HireDate)) - strftime('%Y', date('now')))
    END AS yw
    from Employee
)
SELECT */*,avg(age-yw) AS answer*/ ,(SELECT avg(age-yw)FROM cte1) FROM cte1; /* note adapted to show all data not just average (so avg repeated) */
DROP TABLE IF EXISTS employee /* cleanup */

和结果（注解重新显示所有数据）：-