SQL 使用多个/从属列计算项目频率？

Question

我对 SQL 完全陌生，并且已经阅读了关于 SQL 的 StackOverflow 帖子来尝试解决这个问题，以及其他来源，但无法在 SQL 中做到这一点。开始...

我有一个包含 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     

我需要弄清楚如何计算并更新第三列，即流行度，即导出该水果的州数。目标是产生(对不起，双关语)下表，其中基于上表，“apple”出现在所有 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

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