如何用Vega-Lite按年月统计会员存续数量并生成可视化图表？

I've put together a Vega-Lite configuration that calculates and visualizes the number of active members for each year-month, based on their membership start and end dates. Here's the full working spec:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "datasets": {
    "members": [
      {"id": 2759, "start_date": "2010-10-19", "end_date": "2016-10-31"},
      {"id": 2760, "start_date": "2010-10-19", "end_date": "2014-03-31"},
      {"id": 2761, "start_date": "2010-10-19", "end_date": "2023-03-31"},
      {"id": 2762, "start_date": "2010-10-21", "end_date": "2012-10-31"},
      {"id": 2763, "start_date": "2010-10-23", "end_date": "2015-11-30"},
      {"id": 2764, "start_date": "2010-10-24", "end_date": "2012-10-31"},
      {"id": 2765, "start_date": "2010-10-25", "end_date": "2012-10-30"},
      {"id": 2766, "start_date": "2010-10-30", "end_date": "2012-10-31"},
      {"id": 2767, "start_date": "2018-09-19", "end_date": "2019-10-18"}
    ]
  },
  "data": {"values": [{}]},
  "transform": [
    // Get the earliest start date and latest end date from member data
    {
      "aggregate": [
        {"op": "min", "field": "start_date", "as": "min_start"},
        {"op": "max", "field": "end_date", "as": "max_end"}
      ],
      "from": {"data": "members"}
    },
    // Generate a sequence of first days of each month between min and max dates
    {
      "sequence": {
        "start": {"expr": "datetime(year(datum.min_start), month(datum.min_start), 1)"},
        "stop": {"expr": "datetime(year(datum.max_end), month(datum.max_end) + 1, 1)"},
        "step": "month",
        "as": "yearmonth_date"
      }
    },
    // Flatten the sequence to create one row per year-month
    {"flatten": ["yearmonth_date"]},
    // Cross join with member data to pair each year-month with every member
    {"cross": {"data": "members", "as": ["month", "member"]}},
    // Extract individual fields from the joined objects for easier processing
    {"calculate": "datum.member.start_date", "as": "start_date"},
    {"calculate": "datum.member.end_date", "as": "end_date"},
    {"calculate": "datum.month.yearmonth_date", "as": "yearmonth_date"},
    // Calculate the last day of the current month (for overlap check)
    {"calculate": "datetime(year(datum.yearmonth_date), month(datum.yearmonth_date) + 1, 0)", "as": "month_end"},
    // Filter to keep only members active during the month: membership overlaps with the month
    {"filter": "datum.start_date <= datum.month_end && datum.end_date >= datum.yearmonth_date"},
    // Format year-month as a string for the x-axis
    {"calculate": "timeFormat(datum.yearmonth_date, '%Y-%m')", "as": "yearmonth"},
    // Count active members per year-month
    {
      "aggregate": [{"op": "count", "as": "active_members"}],
      "groupby": ["yearmonth", "yearmonth_date"]
    },
    // Ensure the x-axis is ordered chronologically
    {"sort": {"field": "yearmonth_date", "order": "ascending"}}
  ],
  "mark": "bar",
  "encoding": {
    "x": {
      "field": "yearmonth",
      "type": "ordinal",
      "title": "Year-Month",
      "axis": {"labelAngle": -45}
    },
    "y": {
      "field": "active_members",
      "type": "quantitative",
      "title": "Number of Active Members"
    },
    "tooltip": [
      {"field": "yearmonth", "title": "Year-Month"},
      {"field": "active_members", "title": "Active Members"}
    ]
  }
}

Key Explainers: #

• Cross Join: We pair every year-month with every member so we can check each member's activity status for each month.
• Membership Overlap Check: Instead of checking against a single "yearmonth" point, we verify if the member's membership overlaps with the entire month. This means:
  • Their start date is on or before the last day of the month
  • Their end date is on or after the first day of the month
• Sequence Generation: We automatically generate all year-months between the earliest membership start and latest membership end, so you don't have to manually list them.
• Sorting: We sort by the actual date value (not just the string) to ensure the x-axis is in correct chronological order.

If you need to adjust the condition to strictly check against a specific day (e.g., the first day of the month), you can modify the filter to:

{"filter": "datum.start_date <= datum.yearmonth_date && datum.end_date >= datum.yearmonth_date"}

But the overlap approach is more accurate for counting members active at any point during the month, which is typically what you'd want for this kind of visualization.

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