मैं Postgres का उपयोग करके कुछ विश्लेषण करने की कोशिश कर रहा हूं, जहां मेरे पास 2 टेबल हैं, जिन्हें कहा जाता है: predictionstate और pageviews

predictionstate तालिका:

इस तालिका में निम्नलिखित संरचना का उपयोग करते हुए हमारे एल्गोरिथम परिणामों वाले कॉलम हैं:

  • आईडी ({company_identifier}:{user_identifier})
  • मॉडल (संदर्भ स्ट्रिंग मान)
  • भविष्यवाणी (फ्लोट संख्या 0.0 और 1.0 के बीच)

pageviews तालिका:

इस तालिका में निम्नलिखित संरचना का उपयोग करते हुए उपयोगकर्ता जानकारी है:

  • company_identifier
  • user_identifier
  • पृष्ठदृश्य_current_url_type

प्रश्न

मैं अपने सर्वोत्तम मॉडल के आधार पर डेटा प्राप्त करने की कोशिश कर रहा हूं, यह विश्लेषण करने के लिए कि यह कितना सही है, जहां मूल रूप से मुझे सेगमेंट बनाने और यह गिनने की जरूरत है कि मेरे पास कितने सदस्य हैं। निम्नलिखित कोड यह करता है:

WITH ranges AS (
  SELECT
    myrange::text || '-' || (myrange + 0.1)::text AS segment,
    myrange as r_min, myrange + 0.1 as r_max
  FROM generate_series(0.0, 0.9, 0.1) AS myrange
)
SELECT
  SPLIT_PART(p.id, ':', 1) as company_identifier,
  p.model,
  r.segment,
  COUNT(DISTINCT(SPLIT_PART(p.id, ':', 2))) as "segment_users"
FROM
  ranges r
INNER JOIN predictionstate p ON p.prediction BETWEEN r.r_min AND r.r_max
GROUP BY company_identifier, p.model, r.segment
ORDER BY company_identifier, p.model, r.segment;

लेकिन मेरे पास जो मुद्दा है, क्योंकि मुझे नहीं पता कि यह कैसे करना है, यह है कि प्रत्येक (कंपनी, मॉडल, सेगमेंट) के लिए, और यह डेटा प्राप्त करने की आवश्यकता है कि यह कितना सटीक है, pageviews तालिका और pageview_current_url_type == 'BUYSUCCESS' की पहचान करना।

मैंने क्या कोशिश की, लेकिन काम नहीं किया:

WITH ranges AS (
  SELECT
    myrange::text || '-' || (myrange + 0.1)::text AS segment,
    myrange as r_min, myrange + 0.1 as r_max
  FROM generate_series(0.0, 0.9, 0.1) AS myrange
)
SELECT
  SPLIT_PART(p.id, ':', 1) as company_identifier,
  p.model,
  r.segment,
  COUNT(DISTINCT(SPLIT_PART(p.id, ':', 2))) as "segment_users",
  b.n as "converted_users"
FROM
  ranges r,
  (
    SELECT COUNT(DISTINCT(pvs.user_identifier)) as n
    FROM pageviews pvs
    INNER JOIN (
        SELECT
            SPLIT_PART(id, ':', 1) as company_identifier,
            SPLIT_PART(id, ':', 2) as user_identifier
        FROM predictionstate ps
        WHERE prediction BETWEEN r.r_min AND r.r_max ) users
        ON (
            pvs.user_identifier = users.user_identifier AND
            pvs.company_identifier= users.company_identifier) 
        WHERE pageview_current_url_type = 'BUYSUCCESS'

  ) b
INNER JOIN predictionstate p ON p.prediction BETWEEN r.r_min AND r.r_max
GROUP BY company_identifier, p.model, r.segment
ORDER BY company_identifier, p.model, r.segment;

टीएल; डीआर: मुझे मुख्य क्वेरी उपयोगकर्ताओं के आधार पर जॉइन गिनने की जरूरत है।

संपादित करें:

मैंने एक SQL Fiddle https://www.db-fiddle.com/f/5sQiZD6mHwdnwvVfvL9MAh जोड़ा /0

मैं क्या जानना चाहता हूं, उन segment_users के लिए, उनमें से कितने के पास pageview_current_url_type = 'BUYSUCCESS' है, परिणाम में एक और कॉलम जोड़ें: segmented_really_bought

संपादित करें 2: एक और प्रयास काम नहीं कर रहा है (त्रुटि: कॉलम "p.id" ग्रुप बाय क्लॉज में दिखाई देना चाहिए या एक समग्र फ़ंक्शन में उपयोग किया जाना चाहिए)

WITH ranges AS (
  SELECT
    myrange::text || '-' || (myrange + 0.1)::text AS segment,
    myrange as r_min, myrange + 0.1 as r_max
  FROM generate_series(0.0, 0.9, 0.1) AS myrange
)
SELECT
  SPLIT_PART(p.id, ':', 1) as company_identifier,
  p.model,
  r.segment,
  COUNT(DISTINCT(SPLIT_PART(p.id, ':', 2))) as "segment_users",
  COUNT(b.*) as "converted_users"
FROM
  ranges r
INNER JOIN predictionstate p ON p.prediction BETWEEN r.r_min AND r.r_max
INNER JOIN (
  SELECT users.company_identifier, COUNT(users.user_identifier) AS n
  FROM pageviews
  INNER JOIN (
    SELECT SPLIT_PART(ps.id, ':', 2) AS user_identifier,
           SPLIT_PART(ps.id, ':', 1) AS company_identifier
    FROM predictionstate ps
    WHERE provider_id=47 AND
          prediction > 0.7
   ) users ON (
      pageviews.user_identifier=users.user_identifier AND
      pageviews.company_identifier=users.company_identifier
    )
  WHERE pageview_current_url_type='BUYSUCCESS'
  GROUP BY users.company_identifier
) AS b
ON (
  b.company_identifier = company_identifier
)
GROUP BY company_identifier, p.model, r.segment
ORDER BY company_identifier, p.model, r.segment;

संपादित करें 3: वांछित आउटपुट जोड़ा गया

इस कोड का उपयोग करके जेनरेट किया गया: https://gist.github.com/brunoalano/479265b934a67dc02092fb54a846fe1e

company, model, segment, segment_users, really_bought
company_a, model_a, 0.3-0.4, 1, 3
company_a, model_a, 0.5-0.6, 1, 1
company_a, model_b, 0.2-0.3, 1, 3
company_a, model_c, 0.2-0.3, 1, 1
company_a, model_c, 0.7-0.8, 1, 3
company_b, model_a, 0.3-0.4, 3, 2
company_b, model_b, 0.5-0.6, 2, 1
company_b, model_b, 0.6-0.7, 1, 1
company_b, model_c, 0.5-0.6, 1, 0
company_b, model_c, 0.8-0.9, 1, 1
2
Bruno Alano 1 अक्टूबर 2018, 23:19

2 जवाब

सबसे बढ़िया उत्तर

नमूना आउटपुट के बिना आपको क्या चाहिए, यह बताना मुश्किल है, लेकिन मुझे लगता है कि आप जो खोज रहे हैं वह है:

WITH ranges AS (
  SELECT
    myrange::text || '-' || (myrange + 0.1)::text AS segment,
    myrange as r_min, myrange + 0.1 as r_max
  FROM generate_series(0.0, 0.9, 0.1) AS myrange
)
SELECT
  p.company_identifier,
  p.model,
  r.segment,
  COUNT(DISTINCT(p.user_identifier)) as "segment_users",
  COUNT(CASE WHEN pv.pageview_current_url_type = 'BUYSUCCESS' THEN 1 END) AS segmented_really_bought
FROM
  ranges r
INNER JOIN (
  SELECT
    SPLIT_PART(id, ':', 1) as company_identifier,
    SPLIT_PART(id, ':', 2) as user_identifier,
    model,
    prediction
  FROM
    predictionstate
  ) p ON p.prediction BETWEEN r.r_min AND r.r_max
LEFT JOIN pageviews pv ON 
  p.company_identifier = pv.company_identifier
  AND p.user_identifier = pv.user_identifier
GROUP BY p.company_identifier, p.model, r.segment
ORDER BY p.company_identifier, p.model, r.segment;

आपकी पहेली क्वेरी में परिवर्तन:

  • predictionstate को एक सबक्वेरी से बदल दिया गया है जिसमें हम शामिल होते हैं, जहां हम अलग-अलग कॉलम के रूप में कॉम्पनी और उपयोगकर्ता पहचानकर्ता प्राप्त करने के लिए split_part तर्क करते हैं
  • उन पहचानकर्ताओं को LEFT JOIN से pageviews तक इस्तेमाल किया
  • CASEd COUNT के साथ segmented_really_bought कॉलम जोड़ा
1
Kamil Gosciminski 3 अक्टूबर 2018, 16:51

डेमो: डीबी<>बेला

WITH ranges AS (
  SELECT
    myrange::text || '-' || (myrange + 0.1)::text AS segment,
    myrange as r_min, myrange + 0.1 as r_max
  FROM generate_series(0.0, 0.9, 0.1) AS myrange
), pstate AS (                                         -- A
  SELECT 
    SPLIT_PART(ps.id, ':', 1) AS company_identifier,
    SPLIT_PART(ps.id, ':', 2) AS user_identifier,
    model,
    prediction
  FROM predictionstate ps
)
SELECT 
  company_identifier, model, segment,
  COUNT(DISTINCT user_identifier) as segment_users,    -- B
  -- C: 
  COUNT(user_identifier) FILTER (WHERE pageview_current_url_type = 'BUYSUCCESS') as really_bought
FROM pstate ps
LEFT JOIN ranges r 
ON prediction BETWEEN r_min AND r_max
LEFT JOIN pageviews pv 
USING (company_identifier, user_identifier)
GROUP BY company_identifier, model, segment
ORDER BY company_identifier, model, segment

ए: मैं वास्तव में अनुशंसा करता हूं कि आपके आईडी कॉलम को बेहतर संचालन के लिए दो कॉलम में विभाजित किया जाना चाहिए। यह आपको स्ट्रिंग को विभाजित करने (प्रश्नों को लिखने और उन्हें निष्पादित करने पर) में अधिक समय बचाएगा और यह अधिक पठनीय है। इसलिए मैंने दूसरा सीटीई जोड़ा।

बी: COUNT(DISTINCT) समूह में विशिष्ट उपयोगकर्ताओं की गणना करता है

सी: सभी उपयोगकर्ताओं की गणना करता है (अलग नहीं) लेकिन गिनती से पहले अपेक्षित स्थिति को फ़िल्टर करता है।


मैं सोच रहा था: क्या होगा यदि कोई भविष्यवाणी बिल्कुल सीमा पर है, उदाहरण के लिए 0.3BETWEEN क्लॉज के साथ यह रेंज 0.2-0.3 और रेंज 0.3-0.4 दोनों में शामिल हो जाएगी (क्योंकि BETWEEN बराबर r_min >= x >= r_max)। श्रेणियों को r_min >= x > r_max या r_min > x >= r_max के रूप में परिभाषित करना बेहतर होगा। जैसा कि आपने अपने उदाहरण में उल्लेख किया है, मैंने शामिल किया है लेकिन मैं इसे बदलना पसंद करूंगा। मैं अभी भी नहीं जानता कि किस दिशा में

1
S-Man 3 अक्टूबर 2018, 19:12