SQL使用多个/相关列计算项目频率？

Question

我有一个3列和数千行的表，前2列有数据。第三列当前为空，我需要根据第一列和第二列中已有的数据填充第三列。

假设我在第一栏中有状态，在第二栏中有水果条目。我需要编写一条SQL语句来计算 每种水果来自的不同状态数
，然后将此流行度数字插入到每一行的第三列中。该行中的受欢迎程度数字1表示水果仅来自一个州，受欢迎程度数字4表示该水果来自4个州。所以我的桌子目前是这样的：

state     fruit     popularity

hawaii    apple     
hawaii    apple     
hawaii    banana       
hawaii    kiwi      
hawaii    kiwi      
hawaii    mango        
florida   apple      
florida   apple        
florida   apple        
florida   orange      
michigan  apple     
michigan  apple     
michigan  apricot   
michigan  orange    
michigan  pear      
michigan  pear      
michigan  pear      
texas     apple     
texas     banana    
texas     banana    
texas     banana    
texas     grape

我需要弄清楚如何计算然后更新第三列，即受欢迎程度，这是出口该水果的州数。目标是生成下表（对不起的双关语），根据上表，在所有4个州中都出现“苹果”，在2个州中都出现了橘子和香蕉，而在1个州中只出现了奇异果，芒果，梨和葡萄。状态，因此它们对应的受欢迎程度数字。

state     fruit     popularity

hawaii    apple     4
hawaii    apple     4
hawaii    banana    2   
hawaii    kiwi      1
hawaii    kiwi      1
hawaii    mango     1   
florida   apple     4 
florida   apple     4   
florida   apple     4   
florida   orange    2  
michigan  apple     4
michigan  apple     4
michigan  apricot   1
michigan  orange    2
michigan  pear      1
michigan  pear      1
michigan  pear      1
texas     apple     4
texas     banana    2
texas     banana    2
texas     banana    2
texas     grape     1

我小的程序员头脑说，试图找到一种在某种脚本中循环遍历数据的方法，但是对SQL和数据库有所了解，似乎您并没有在SQL中编写冗长而缓慢的循环脚本。我什至不确定是否可以？但是相反，在SQL中有更好/更快的方法可以做到这一点。

任何人都知道如何在SQL语句中为每一行计算和更新第三列，在此称为流行度，并与每种水果所来自的状态数相对应？感谢您的阅读，非常感谢您的帮助。

到目前为止，我已经在下面尝试了这些SQL语句，这些语句可以输出，但并不能完全满足我的需要：

--outputs those fruits appearing multiple times in the table
SELECT fruit, COUNT(*)
  FROM table 
 GROUP BY fruit
HAVING COUNT(*) > 1
 ORDER BY COUNT(*) DESC

--outputs those fruits appearing only once in the table
SELECT fruit, COUNT(*)
  FROM table 
 GROUP BY fruit
HAVING COUNT(*) = 1

--outputs list of unique fruits in the table
SELECT COUNT (DISTINCT(fruit))
  FROM table

Answer 1

如果您只想使用优先级来更新表，它将看起来像：

update my_table x
   set popularity = ( select count(distinct state) 
                        from my_table
                       where fruit = x.fruit )

如果要选择数据，则可以使用分析查询：

select state, fruit
     , count(distinct state) over ( partition by fruit ) as popularity
  from my_table

这提供了每个水果不同状态的数量。