Twelve public Chinese-music charts, nine platforms, two snapshots a day: roughly 570 rows every 12 hours, and no shared track ID anywhere — the three TME properties have separate namespaces, and asking one for the other's ID silently returns a different song. So "this song is on 6 of 9 platforms" is not a join. It is a text decision, and every number downstream inherits whatever that decision got wrong.

Live output, if you want to look at the result before the code: https://xingyuan.beer/en?src=devto (charts, rank deltas, weekly diffs — read-only, no signup).

The problem: nine charts, zero shared IDs

Twice a day I take a read-only snapshot of 12 public music charts across 9 platforms that publish Chinese-music charts — NetEase Cloud Music, QQ Music, Kugou, Kuwo, Qishui, Douyin, Weibo, Bilibili and Apple Music. That is roughly 570 rows per snapshot. The interesting part is not fetching the pages. It is deciding when two rows are the same song.

There is no track ID shared across all nine. The three TME properties (QQ Music, Kugou, Kuwo) have completely separate namespaces; asking one for the other's ID silently returns a different song. So "this track is on 6 of 9 platforms" has to be computed from text, not from identifiers.

The text is messy in predictable ways:

  • 晴天, 晴天 (Live), 晴天（剪辑版） — handled by the key below
  • 晴天 - Live — not handled: it becomes 晴天live, a known gap
  • 七里香 feat. 杨瑞代 vs 七里香
  • A/B vs A、B vs A & B for the same collaboration
  • @someone创作的原声 — a Douyin sound title that is not a song title at all
  • Chinese and English artist aliases: 周杰伦 vs Jay Chou

I wanted the daily pipeline to be deterministic and auditable, not "the model thinks these are the same". Here is what I ended up with.

Layer 1: one normalized key per track

The key is normalized title | sorted, normalized artists. Normalization is deliberately boring:

import re, unicodedata _BRACKETS = re.compile(r"[\(\（\[\【].*?[\)\）\]\】]") _FEAT = re.compile(r"\b(?:feat|ft|featuring)\.?\s+.*$", re.IGNORECASE) _NON_WORD = re.compile(r"[^0-9a-z\u4e00-\u9fff]+") def normalize_text(value: str) -> str: s = unicodedata.normalize("NFKC", value or "").casefold() s = _BRACKETS.sub(" ", s) # drop "(Live)", "（剪辑版）", "【...】"  s = _FEAT.sub(" ", s) # drop "feat. X" tails  return _NON_WORD.sub("", s) # keep ASCII alnum + CJK only  def dedupe_key(name: str, artists: list[str]) -> str: n = normalize_text(name) a = "/".join(sorted({normalize_text(x) for x in artists if normalize_text(x)})) return f"{n}|{a}" if (n or a) else ""

So 晴天 (Live) / 周杰伦 and 晴天 / 周杰伦 collapse to 晴天|周杰伦. The same title by a different artist does not: the artist set is part of the key, which keeps covers apart without any extra rules.

Two deliberate trade-offs:

  1. Brackets are dropped wholesale. That merges a live version with the studio version, which is technically wrong. For chart aggregation it is usually the right call — charts treat them as one song — and the raw platform rows are still stored, so nothing is lost for audit. It also means (电视剧《…》主题曲) disappears, which is fine for identity and bad for display. Display uses the raw title, not the key.
  2. Artist aliases are not merged. 周杰伦 and Jay Chou produce different keys, so a track can split into two identities. That undercounts cross-platform presence, but it never invents a merge. Under-counting is recoverable; a wrong merge poisons every downstream number.

Layer 2: fuzzy matching, kept offline

There is a second scoring function in the repo for the cases the exact key cannot handle: name similarity (0.45), artist Jaccard overlap (0.30), duration closeness (0.20), album equality (0.05), with weights renormalized when a field is missing:

def score(a, b): name_sim = SequenceMatcher(None, norm_name(a["name"]), norm_name(b["name"])).ratio() art_sim = jaccard(norm_artists(a["artists"]), norm_artists(b["artists"])) dur_sim = 1.0 if abs(a["duration"] - b["duration"]) <= 3 else ... total = 0.45*name_sim + 0.30*art_sim + 0.20*dur_sim + 0.05*album_eq if a["duration"] and b["duration"] and abs(a["duration"] - b["duration"]) > 20: total = min(total, 0.55) # original vs 65s edit are different recordings  return total

The 20-second cap matters: a 269-second original and a 65-second clip can share a title and an artist, and should never be merged.

But this scorer is not in the daily path. Two reasons. First, 570 rows is small enough for pairwise comparison, but threshold tuning on Chinese titles is fragile: one bad threshold merges five different 泪海 recordings, and once you have written metrics against that merge, unwinding it is real work. Second, determinism. Exact keys can be recomputed from the raw rows by anyone; a fuzzy score with a tuned threshold is an opinion. The fuzzy matcher stays offline, used to review alias candidates before adding a manual mapping.

If you want the output rather than the machinery: the English charts view is https://xingyuan.beer/en?src=devto and the raw weekly diff samples are https://xingyuan.beer/reports.

Snapshot diffing without an event table

Storage is SQLite. One row per (platform, board, captured_at, rank), with an idempotent upsert:

INSERT INTO charts (platform, board, captured_at, rank, name, artists, dedupe_key, ...) VALUES (?, ?, ?, ?, ?, ?, ?, ...) ON CONFLICT (platform, board, captured_at, rank) DO UPDATE SET ...

The metrics are derived per snapshot, not appended as events: rank_delta = prev_rank - rank, is_new = 1 when the key was absent from the previous snapshot, cross_platform = the number of distinct platforms carrying that key in the same snapshot. Because they are derived, recomputing a snapshot is safe and a parser fix can be replayed.

The one thing that must be stable is captured_at. All boards in a batch share it, because deltas are computed between snapshots. If one board fails and the next run starts a new batch with a fresh timestamp, you get a partial snapshot whose deltas are wrong in a way that looks plausible. So a batch is explicit state: which boards are in, which failed, and what the shared timestamp is. An unfinished batch resumes with the same captured_at for up to six hours; after that it is too stale to be useful and a new batch starts.

Fetching: throttle, classify, and don't fight back

The HTTP layer is per platform: a 0.35s minimum gap per host, two retries with exponential backoff and jitter. The important part is classification:

  • 429 → rate limited, do not retry; set a cooldown for that board.
  • 401 / 403 / 412 → blocked, do not retry. (Bilibili in particular uses 412 for risk control.)
  • 5xx → server, retry with backoff.
  • network errors → retry.

Retrying a risk-control response just gets you blocked harder. Cooldowns live in a state file next to the database, so a restart does not forget that a board asked us to go away for a while.

There is also a publication gate: a weekly diff is only generated when the last scheduled slot is complete. A partial batch is not "mostly fine" — it silently drops a board, and a diff that quietly lost Weibo is worse than a diff that is a few hours late.

Things that actually bit me

  • Douyin's board is a challenge-word board, not a song chart. Entries are tags people use in videos. Most are song titles, some are memes, and @user创作的原声 is not a title at all. Cross-platform matching for Douyin is therefore approximate by construction.
  • Weibo's music board is aggregated from posts, not an official label chart. It is comparable as "what people post about", not as "what is charting".
  • "New" means new since the previous snapshot, which is 12 hours earlier, not "new release". I had to rename the column in my own head before the UI stopped lying.
  • Artist splitting is per adapter, and not uniform. Some adapters split A/B or A、B into a list; Apple returns 周杰伦 & 五月天 阿信 as a single string. Identical collaborations can therefore land on different keys. A shared splitter is on the list; until then the key is deliberately conservative. Also, A feat. B normalizes to just A — the same regex that merges live/studio variants eats the feature credit.
  • Time zones. Slots are 09:40 and 21:40 China time. A delta is 12 hours, not a day. Any "daily" statement has to say which two snapshots it compares.

What I would do next

The honest answer is that text normalization has a ceiling. Artist aliases need a curated map (there is no string metric that knows 周杰伦 is Jay Chou). The robust way to confirm "same recording" is audio fingerprinting — chroma/MFCC features plus DTW — which exists in the repo as an offline tool but is far too heavy to run on every row of every snapshot. A cheaper next step is a small alias table plus a review queue for new high-cross-platform keys.

For now, the deterministic key handles the bulk of it, the fuzzy scorer stays where it cannot poison aggregates, and every number on the site is traceable back to raw rows and a snapshot timestamp.

If you want to see the output: the English landing page is at https://xingyuan.beer/en?src=devto, the public Chinese dashboard is https://xingyuan.beer/trends, and there are weekly diff samples at https://xingyuan.beer/reports. All chart data is public; the code is not open source, but the methodology is documented at https://xingyuan.beer/data.

Charts use different methodologies; rank changes are not sales, streams or revenue. Disclosure: collection runs as a scheduled job, and this write-up was assembled with AI assistance from the running code and real snapshots.