Hi I am using SQL server. I have a set of data and I am trying to use window functions to count the instances of something. I am then running the window function again and dividing it against a row count window function to try and get an average. However, I keep getting 0. Is it possible to run a window function against another in the same column?
嗨，我正在使用SQL服务器。我有一组数据，我正在尝试使用窗口函数来计算某些事物的实例。然后我再次运行窗口函数并将其除以行计数窗口函数以尝试获得平均值。但是，我一直得到0.是否可以在同一列中对另一个运行窗口函数？
COUNT(city) OVER (partition by state)/Count(*) over (partition by total)*100 AS AVG
is something like this possible? When i break it apart and run each individually it works, but when i combine them i get 0 in the column
这样的事情可能吗？当我将它分开并单独运行时，它可以工作，但是当我将它们组合起来时，我在列中得到0
1 个解决方案
#1
3
This is because of integer arithmetic -- nothing to do with window functions. Write this as:
这是因为整数运算 - 与窗口函数无关。把它写成：
COUNT(city) OVER (partition by state) * 100.0 / Count(*) over (partition by total) AS AVG
The 100.0 puts a decimal point in the arithmetic. So, 1/2 is 0. But 1.0/2 is 0.5.
100.0在算术中加上小数点。因此，1/2是0.但1.0 / 2是0.5。
#1
3
This is because of integer arithmetic -- nothing to do with window functions. Write this as:
这是因为整数运算 - 与窗口函数无关。把它写成：
COUNT(city) OVER (partition by state) * 100.0 / Count(*) over (partition by total) AS AVG
The 100.0 puts a decimal point in the arithmetic. So, 1/2 is 0. But 1.0/2 is 0.5.
100.0在算术中加上小数点。因此，1/2是0.但1.0 / 2是0.5。