关于基于gdsname等字段统计origin-destination相关条目数的技术咨询

Let's break down each requirement and share practical SQL queries that work across most relational databases (MySQL, PostgreSQL, SQL Server, etc.).

Requirement 1: Count Unique Origin-Destination Pairs per Gdsname #

You have a table with origin, destination, and gdsname columns, and need to group by gdsname to count how many distinct origin-to-destination routes exist in each group.

Here's the query:

SELECT 
    gdsname,
    COUNT(DISTINCT CONCAT(origin, '|', destination)) AS unique_route_count
FROM 
    your_table_name
GROUP BY 
    gdsname;

Quick Notes: #

• If your database supports row value expressions (like PostgreSQL), you can use a cleaner syntax instead of concatenation:

  SELECT 
      gdsname,
      COUNT(DISTINCT (origin, destination)) AS unique_route_count
  FROM 
      your_table_name
  GROUP BY 
      gdsname;
  

• Replace your_table_name with your actual table name.
• The result will show each gdsname alongside the number of unique origin-destination routes under it.

Requirement 2: Count Origin-Destination Entries per Gdsname (Ignoring Sourcemedia) #

Your table includes a sourcemedia column (universal for all origin-destination pairs), and you need to count entries for each origin-destination route, split by the gdsname dimension.

This query groups by gdsname, origin, and destination to get counts for each route-gdsname combination:

SELECT 
    gdsname,
    origin,
    destination,
    COUNT(*) AS entry_count
FROM 
    your_table_name
GROUP BY 
    gdsname,
    origin,
    destination;

Quick Notes: #

• If you want the total number of entries per gdsname (summing all routes under it), just remove origin and destination from the select and group by clauses:

  SELECT 
      gdsname,
      COUNT(*) AS total_entries
  FROM 
      your_table_name
  GROUP BY 
      gdsname;
  

• Don't forget to swap your_table_name with your actual table's name.

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