如何绕过默认参数实现SQL日期范围的趋势话题评分

编辑（再次更新） #

添加了表格和两张截图（一张是Google Sheets图表，另一张展示Data Studio中的多个问题）以辅助说明当前情况。

简短说明 #

已创建一个参数，用于基于日期范围过滤器对热门话题进行评分，但希望能显示一段日期范围内的数据，而非仅单个日期的数据。理论上可将参数设为包含大量日期的复选框，但这既低效也不具备长期可持续性。

声明 #

接触SQL和Data Studio仅约一周时间。

详细说明 #

正在从特定客户数据集中追踪随时间变化的趋势，希望实现：当用户调整时间范围时，各类话题的“评分”取决于结束日期。例如，每当提及话题“Recession（经济衰退）”时，会为其赋予一个评分，该评分会根据提及时间进行加权。设定最高评分为365，超过一年的数据评分为空。因此，如果“Recession”被提及两次，一次在一周前，一次在今天，其平均评分为361.5；而如果话题“Talent Management（人才管理）”被提及两次且均在今天，其评分则为365，以此类推。需要追踪的话题已超过50个，覆盖50多个特定社群。

示例数据 #

该评分会影响气泡图的气泡大小，图表Y轴为提及话题的唯一群组数量，X轴基于平均评分范围。

目标效果 #

通过范围滑块查看哪些话题在群组中最常见、哪些是新兴趋势、哪些是过时趋势。用户（其他部门同事）可以调整日期范围，直观看到气泡的位置和大小变化。（附Google Sheets静态图表示例）

还可利用相同的数据和字段，基于时间范围内的加权平均值衡量各话题在群组中的讨论占比。

Google Sheets实现方式 #

在Google Sheets中，通过xLookup实现：在名为'scales'的工作表中，一列是0-365，另一列是365-0，然后在某单元格输入任意日期作为时间点，该日期会影响所有评分、表格和图表等，使用公式：=xlookup((point_in_time - entry_date), 'scales'!A:A, 'scales'!B:B, "0")

Data Studio当前尝试 #

在Data Studio的自定义SQL中，使用了以下代码：

SELECT
  *
FROM
  `qRaw_data`
where
    DATE(_entry_dates_) between
  parse_date('%Y%m%d', @DS_START_DATE) and
  parse_date('%Y%m%d', @DS_END_DATE)
AND
@pit_date_diff = date_diff(
    parse_date('%Y%m%d', @ds_end_date),
    _entry_dates_,
    day
  )

随后创建了一个名为time_score的字段，公式为：
avg((Pit_Date_Diff-365)*(-1))

一直在谷歌和YouTube上查找解决方案，认为要么需要找到覆盖@pit_date_diff默认值的方法，要么需要在自定义查询中使用CASE WHEN语句（例如当date_diff为1时返回365，以此类推），但尝试时出现了各种错误。

核心需求 #

希望能基于所有录入日期计算所有话题的平均值，而非仅显示与输入参数字段匹配的数据。（附当前受参数限制仅能显示特定录入日期的数据的截图）

感谢所有帮助，接触Data Studio仅一周，搜索相关内容已头昏脑胀。可能有更合理的方法实现这些需求，期待得到解决方案。

提前致谢。

内容的提问来源于stack exchange，提问作者Taylor Luczak