我有一个正在尝试转换为配置单元的sql server查询，但使用分组集无法获得预期的结果。总的目标是能够提取所有需要的信息来对每个值进行小计，为每个字段生成一次创建报告所需的数据。出于某种原因，hive不管在什么地方都会为field2返回y，因为sql server只返回y一次。
在sql server中，查询如下所示：

select 
    f.field1, f.field2, f.field3, count(*) count 
from 
    (select
         case 
            when field1 > '' then 'Selected'
            else '' 
         end field1,
         field2, field3,
         row_number() over (partition by ds.field4, ds.field5, ds.field6 order by ds.keyfield asc) recid1
     from 
         #data
     where 
         field3 in ('12345', '67890')
         and field2 = 'Y'
         and field1 > '') f 
where 
    recid1 = 1
group by 
    grouping sets ((),f.field1, f.field2, f.field3)

这将返回以下结果：

field1  |field2|field3|count
NULL    |NULL  |12345 |10
NULL    |NULL  |67890 |2
NULL    |NULL  |NULL  |12
NULL    |Y     |NULL  |12
Selected|NULL  |NULL  |12

当我尝试在配置单元中运行类似的查询时，会得到不同的结果：

select f.field1, f.field2, f.field3,count(*) count 
from ( 
    select 
        case 
            when field1 > '' then 'Selected' 
            else '' 
        end field1,
        field2,
        field3,
        row_number() over (partition by ds.field4, ds.field5, ds.field6 order by ds.keyfield asc) recid1 
    from mydb.mytable ds 
where
    field3 in ('12345','67890')
    and ds.field2 = 'Y'
    and ds.field1 > '' 
)f where recid1=1
group by f.field1, f.field2, f.field3 
grouping sets ((),f.field1, f.field2, f.field3)

结果：

+-----------+-----------+-----------+--------+
| f.field1  | f.field2  | f.field3  | count  |
+-----------+-----------+-----------+--------+
| NULL      | Y         | 12345     | 10     |
| Selected  | Y         | NULL      | 12     |
| NULL      | Y         | NULL      | 12     |
| NULL      | Y         | 67890     | 2      |
| NULL      | Y         | NULL      | 12     |
+-----------+-----------+-----------+--------+

样本数据：

create table #data (field1 varchar(10),field2 varchar(10),field3 varchar(10),field4 varchar(10),field5 varchar(10),field6 varchar(10),keyfield varchar(10))
insert into #data select '1','Y','12345','1','1','1','1'
insert into #data select '2','Y','67890','2','2','2','2'
insert into #data select '3','Y','67890','3','3','3','3'
insert into #data select '4','Y','12345','4','4','4','4'
insert into #data select '5','Y','12345','5','5','5','5'
insert into #data select '6','Y','12345','6','6','6','6'
insert into #data select '7','Y','12345','7','7','7','7'
insert into #data select '8','Y','12345','8','8','8','8'
insert into #data select '9','Y','12345','9','9','9','9'
insert into #data select '10','Y','12345','10','10','10','10'
insert into #data select '11','Y','12345','11','11','11','11'
insert into #data select '12','Y','12345','12','12','12','12'