sql - 将数据类型更改为 float 并四舍五入为 2 位小数

Question

表格:

people(id, name)
job (id, people_id, job_title, salary) 

Goal: Display each unique job, the total average salary (FLOAT and rounded to 2 decimal places), the total people and the total salary (Float and rounded to 2 decimal places) and order by highest average salary.

因此，挑战在于将转换类型保持为 float ，同时将其四舍五入到小数点后两位。

我已经将它四舍五入到小数点后两位，但它不是 float 。我已将其调整到 float 位置，但我无法将其四舍五入到小数点后两位。

我的尝试:

尝试 1:

SELECT 
  distinct(j.job_title) as job_title,
  to_char(AVG(j.salary)::FLOAT, 'FM999999990.00') as average_salary,
  COUNT(p.id) as total_people,
  CAST (SUM(j.salary) AS FLOAT) as total_salary
  FROM people p
    JOIN job j on p.id = j.people_id
  GROUP BY j.job_title
  ORDER BY total_salary

问题:仍然说它不是 float 的

尝试 2:

SELECT 
  distinct(j.job_title) as job_title,
  CAST (AVG(j.salary) AS FLOAT) as average_salary,
  COUNT(p.id) as total_people,
  CAST (SUM(j.salary) AS FLOAT) as total_salary
  FROM people p
    JOIN job j on p.id = j.people_id
  GROUP BY j.job_title
  ORDER BY total_salary

问题:没有四舍五入到小数点后两位

尝试 3:

SELECT 
  distinct(j.job_title) as job_title,
  ROUND (AVG(CAST(j.salary as FLOAT)), 2)) as average_salary,
  COUNT(p.id),
  ROUND (SUM(CAST(j.salary as FLOAT)), 2)) as total_salary
  FROM people p
    JOIN job j on p.id = j.people_id
  GROUP BY j.job_title
  ORDER BY total_salary

我收到一条错误消息，提示我需要添加显式转换类型，这让我尝试了第 1 次尝试。

Answer 1

答案取决于 salary 列的实际数据类型。关键是Postgres中的round()不允许float(只支持numeric类型)。

如果您正在处理numeric 数据类型，那么您可以先使用round()，然后转换为float:

round(avg(salary), 2)::float

如果您正在处理一个 float 列，那么您需要先转换聚合函数的结果，然后再对其使用 round():

round(avg(salary)::numeric, 2)::float