基于fenotipos与atributos表统计符合多条件的女性样本数

Hey there! Let's break down how to calculate the number of samples that meet your criteria. First, let's recap the tables we're working with, then build the solution step by step.

Step 1: Table Data Overview #

First, here's the data from both tables for reference:

fenotipos Table

atributos Table

Step 2: Key Conditions to Apply #

We need to:

1. Join the tables: The id in fenotipos is the prefix for SAMPID in atributos (e.g., K-562 matches all SAMPID values starting with K-562).
2. Filter for female samples: sex = 2
3. Filter for DTHHRDX value 1: DTHHRDX = 1
4. Filter for blood-related SMTS: Match any SMTS value that contains "blood" (case-insensitive to cover both Blood vessel and Blood_dry).

Step 3: The SQL Query #

Here's the query that implements all these rules:

SELECT COUNT(DISTINCT a.SAMPID) AS sample_count
FROM fenotipos f
JOIN atributos a ON a.SAMPID LIKE CONCAT(f.id, '%')
WHERE f.sex = 2
  AND f.DTHHRDX = 1
  AND LOWER(a.SMTS) LIKE '%blood%';

Let's break this down:

• JOIN atributos a ON a.SAMPID LIKE CONCAT(f.id, '%'): Links each fenotipos record to all matching atributos samples using the prefix match.
• WHERE f.sex = 2 AND f.DTHHRDX = 1: Applies the filters for female samples and the required DTHHRDX value.
• LOWER(a.SMTS) LIKE '%blood%': Ensures we catch any SMTS value with "blood" regardless of capitalization.
• COUNT(DISTINCT a.SAMPID): Counts each unique sample once, even if there were multiple matches (though in this case, each sample is unique).

Step 4: Verify the Result #

Running this query will return 2 as expected. The matching samples are:

• K-562-SM-26GMQ (SMTS: Blood vessel)
• K-562-SM-2AXTU (SMTS: Blood_dry)

Both are linked to K-562 in fenotipos, which meets sex=2 and DTHHRDX=1, and their SMTS values include "blood".

内容的提问来源于stack exchange，提问作者Jose Gracia Rodriguez