有没有一种方法可以将 SQL 查询中的一列中的空值分配到另一列中？

发布于 2025-01-11 11:59:34 字数 1468 浏览 0 评论 0原文

我有什么

地区	地区	潜艇
NULL	NULL	500
Akmola	Kokshetau	150
Almaty	Taldykorgan	500
Aktobe	Aktobe	400

我想要获得什么

地区	地区	潜艇
Akmola	Kokshetau	316
Almaty	Taldykorgan	666
Aktobe	Aktobe	566

有没有办法在列中平均分配价值（潜艇/收入）其他列中为 NULL （区），以及同一列中不为空的其他值。

原文

What I have

region	district	subs
NULL	NULL	500
Akmola	Kokshetau	150
Almaty	Taldykorgan	500
Aktobe	Aktobe	400

What I want to get

region	district	subs
Akmola	Kokshetau	316
Almaty	Taldykorgan	666
Aktobe	Aktobe	566

Is there a way to equally distribute value in column (subs / revenue) that is NULL in other column (district), among other values that are not null in the same column.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暗藏城府 2025-01-18 11:59:34

试试这个：

SELECT region
     , district
     , subs + sum(subs) FILTER (WHERE region IS NULL) OVER() / count(*) FILTER (WHERE region IS NOT NULL) OVER () :: integer AS subs
  FROM your_table

Try this :

SELECT region
     , district
     , subs + sum(subs) FILTER (WHERE region IS NULL) OVER() / count(*) FILTER (WHERE region IS NOT NULL) OVER () :: integer AS subs
  FROM your_table

回复收藏 0 原文

故乡的云 2025-01-18 11:59:34

不确定我完全理解这个问题，但我猜你想要类似的东西：

select region
     , district
     , subs + (select subs/(select count(1) from t where region is not null) from t where region is null)
from t
where region is not null;

region  district    (No column name)
Akmola  Kokshetau   316
Almaty  Taldykorgan 666
Aktobe  Aktobe      566

Not sure I fully understand the question, but I guess you want something like:

select region
     , district
     , subs + (select subs/(select count(1) from t where region is not null) from t where region is null)
from t
where region is not null;

region  district    (No column name)
Akmola  Kokshetau   316
Almaty  Taldykorgan 666
Aktobe  Aktobe      566

回复收藏 0 原文

~没有更多了~