SQL统计需求：指定vehicleViewId在value_map首/第三位的会话数

需求说明 #

作为SQL新手，需完成以下统计任务：

• 统计符合条件的会话中，value_map（类型为map<string, string>）内vehicleViewId为20054133、20054595、20055263的记录
• 区分vehicleViewId对应的index是第1位（index=1）还是第3位（index=3），最终输出两列：top1_count（第1位的数量）、top3_count（第3位的数量）

原尝试SQL（未达预期） #

SELECT
  city_id,
  name,
  type,
  value_map,
FROM rider_event
WHERE
  name = 'request_confv2_product_tap'
  AND type = 'tap'
  AND element_at(map_keys(value_map), 3) = 'vehicleViewId'
  AND element_at(map_values(value_map), 3) IN ('20054133', '20054595', '20055263')
  AND datestr BETWEEN '2023-01-01' AND '2023-09-30';

示例value_map数据 #

pricingResponsePayload.responseId47789a1e-c337-4fba-8305-8cbf3ba8fed2index1vehicleViewId11107responsePayload.responseIda15f052d-63a1-45a1-ab1a-5ac65f255ea5previouslySelectedtruescopeUuid017d7868-6c1f-4e32-9bae-ca177e1960aahostListRECOMMENDED_LIST
pricingResponsePayload.responseId2352782f-56f5-4508-8fe1-a8e30799ea06index2vehicleViewId4185responsePayload.responseId2c7417ce-2774-48b6-869c-3042eac0685bpreviouslySelectedfalsescopeUuid3379e93e-c85d-4df8-addc-04b47b959bc0hostListRECOMMENDED_LISTvalue_map_schema_nameProductSelectionProductTapPayload
previouslySelectedfalsescopeUuid789c0f8c-8b94-4cae-a2d2-ab2fd955978ahostListRECOMMENDED_LISTvalue_map_schema_nameProductSelectionProductTapPayloadindex2vehicleViewId20042977responsePayload.responseHash8676db13786afb743cf270621d3f21b32632717afa30ecf31f99d52c630cf68dresponsePayload.responseId7638ccb4-55a0-4255-a775-1f9a24fd0947
index1vehicleViewId2875responsePayload.responseId26f8afd9-243c-4b4b-a421-1eda0c6a31c5previouslySelectedtruescopeUuid3dee9a49-1e40-45d2-b6c0-2ab0a5d7fa53hostListRECOMMENDED_LISTpricingResponsePayload.responseIdf68f4acd-ea74-4000-b25b-a9f763f14d4b
index0vehicleViewId10002446responsePayload.responseHashf9ebf5181099439ebcac940a98bacf476006795bd58824102ac02a17ead6d383responsePayload.responseId91f253f9-fca7-438c-9565-4da938953465visibleBoltOns[]previouslySelectedtruescopeUuidBB1F91B7-EEA1-45E6-A88D-E729D046FE56hostListRECOMMENDED_LIST
vehicleViewId20047223responsePayload.responseId9885b55e-4442-48b6-86fd-99e400db37aepreviouslySelectedtruescopeUuidac1966bb-c463-416b-a313-7be75f29cd9fhostListRECOMMENDED_LISTpricingResponsePayload.responseId8f5344c7-733e-4d43-8a1e-26b8b6118a8findex1
previouslySelectedfalsescopeUuidd8613ca0-c17e-4afd-89e5-f85a685db962hostListRECOMMENDED_LISTindex1vehicleViewId1142responsePayload.responseHash211a99b165c6fceb8f3bcb974765f3cb9816790342149b6f66e7b4863c7730ccresponsePayload.responseId16c577ec-a02a-49df-8b53-87970330be97
hostListRECOMMENDED_LISTpricingResponsePayload.responseIdfb3e3dba-a406-4240-83d7-c7b3406b60baindex1vehicleViewId20004819responsePayload.responseId3827464d-14c9-41bf-869d-8d3b8722afcfpreviouslySelectedtruescopeUuid815797d9-2507-4355-8853-0d9178bdcc09
vehicleViewId2875responsePayload.responseHashbebcc6749eb7c08e299ac843d334a590a9901c72734ea62f838d9ad83d097741responsePayload.responseId579aa538-7c39-43d2-a289-d81ab1d0423fpreviouslySelectedtruescopeUuid37a57acf-3a8d-4e68-b145-eed30eb9e84dhostListRECOMMENDED_LISTindex0
responsePayload.responseId397355c7-afae-4d4d-a56e-fcce0745b879previouslySelectedtruescopeUuid8d51757d-8ae7-4fb3-8ced-63f766f1d353hostListRECOMMENDED_LISTindex0vehicleViewId2875responsePayload.responseHash4ad5b3eca315922fb6f1e7c9513c60ecbbfc3a1b7463d602d1abee4a2cbf80d2

正确SQL实现 #

原SQL的问题：仅筛选了vehicleViewId处于map第3个位置的记录，未做统计聚合，也未区分第1/3位的情况。正确实现如下：

全局统计版本 #

SELECT
  SUM(CASE WHEN value_map['index'] = '1' AND value_map['vehicleViewId'] IN ('20054133', '20054595', '20055263') THEN 1 ELSE 0 END) AS top1_count,
  SUM(CASE WHEN value_map['index'] = '3' AND value_map['vehicleViewId'] IN ('20054133', '20054595', '20055263') THEN 1 ELSE 0 END) AS top3_count
FROM rider_event
WHERE
  name = 'request_confv2_product_tap'
  AND type = 'tap'
  AND datestr BETWEEN '2023-01-01' AND '2023-09-30'
  -- 可选：过滤无index/vehicleViewId的记录，避免空值干扰
  AND value_map['index'] IS NOT NULL
  AND value_map['vehicleViewId'] IS NOT NULL;

按city_id分组统计版本 #

如果需要按城市维度统计，可添加分组：

SELECT
  city_id,
  SUM(CASE WHEN value_map['index'] = '1' AND value_map['vehicleViewId'] IN ('20054133', '20054595', '20055263') THEN 1 ELSE 0 END) AS top1_count,
  SUM(CASE WHEN value_map['index'] = '3' AND value_map['vehicleViewId'] IN ('20054133', '20054595', '20055263') THEN 1 ELSE 0 END) AS top3_count
FROM rider_event
WHERE
  name = 'request_confv2_product_tap'
  AND type = 'tap'
  AND datestr BETWEEN '2023-01-01' AND '2023-09-30'
  AND value_map['index'] IS NOT NULL
  AND value_map['vehicleViewId'] IS NOT NULL
GROUP BY city_id;

关键说明 #

1. 直接提取map值：用value_map['key']直接获取map中对应key的值，无需依赖element_at按位置索引（map是无序结构，按位置索引不可靠）
2. 条件聚合统计：通过CASE语句判断符合条件的记录，用SUM聚合得到对应数量
3. 空值过滤：添加条件排除无index或vehicleViewId的记录，避免统计偏差

内容的提问来源于stack exchange，提问作者Vikas Sharma