根据上一个问题的答案选择MCQ答案计数 [英] Select MCQ answer count based on previous question's answer

查看：91
本文介绍了根据上一个问题的答案选择MCQ答案计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

让我们采用具有以下结构的PostgreSQL数据库:

Let's take a PostgreSQL DB with the following structure:

(从上到下的所有关系都是 OneToMany )

(all the relations from top to down are OneToMany)

Brandlift 始终精确地有2个 brandlift_question ，每个都有1个 brandlift_answer (自身具有许多 brandlift_answer_content )， brandlift_respondent

Where a Brandlift always has exactly 2 brandlift_question each having 1 brandlift_answer (itself having many brandlift_answer_content) by brandlift_respondent

目标:

给出一个品牌" (我们称其为旗舰品牌")
和 brandlift.campaign_id ，

Given a 'brand' (let's call it the "flagship brand")
and a brandlift.campaign_id,

为此品牌提升的每个品牌，
按受访者细分检索，
brand.id 的数量=第二个(右侧)问题 答案的"answer_content.brand_id" ，
其中同一受访者的前一个(左)问题 答案，其 answer_content.brand_id 等于旗舰品牌" id

for each brand of this brandlift,
retrieve by respondent segment,
the count of brand.id = 'answer_content.brand_id' of the second (right) question answers,
where the previous (left) question answer of the same respondent has an answer_content.brand_id equal to the "flagship brand" id

样本数据(转储):
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a127374b9a327204db40> /p>

Sample data (dump) :
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a127374b9a327204db40dc9f4e769fc1

-- -- PostgreSQL database dump -- -- Dumped from database version 12.1 -- Dumped by pg_dump version 12.3 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off; SET default_tablespace = ''; SET default_table_access_method = heap; -- -- Name: brandlift; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.brandlift ( campaign_id uuid NOT NULL, respondent_goal integer NOT NULL ); ALTER TABLE public.brandlift OWNER TO postgres; -- -- Name: COLUMN brandlift.campaign_id; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.brandlift.campaign_id IS '(DC2Type:uuid)'; -- -- Name: brandlift_answer; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.brandlift_answer ( id integer NOT NULL, question_id uuid NOT NULL, respondent_id integer NOT NULL ); ALTER TABLE public.brandlift_answer OWNER TO postgres; -- -- Name: COLUMN brandlift_answer.question_id; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.brandlift_answer.question_id IS '(DC2Type:uuid)'; -- -- Name: brandlift_answer_content; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.brandlift_answer_content ( id integer NOT NULL, answer_id integer NOT NULL, brand_id integer ); ALTER TABLE public.brandlift_answer_content OWNER TO postgres; -- -- Name: brandlift_answer_content_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.brandlift_answer_content_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.brandlift_answer_content_id_seq OWNER TO postgres; -- -- Name: brandlift_answer_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.brandlift_answer_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.brandlift_answer_id_seq OWNER TO postgres; -- -- Name: brandlift_brand; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.brandlift_brand ( id integer NOT NULL, campaign_id uuid NOT NULL, name character varying(255) NOT NULL ); ALTER TABLE public.brandlift_brand OWNER TO postgres; -- -- Name: COLUMN brandlift_brand.campaign_id; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.brandlift_brand.campaign_id IS '(DC2Type:uuid)'; -- -- Name: brandlift_brand_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.brandlift_brand_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.brandlift_brand_id_seq OWNER TO postgres; -- -- Name: brandlift_question; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.brandlift_question ( id uuid NOT NULL, campaign_id uuid NOT NULL, title character varying(255) NOT NULL ); ALTER TABLE public.brandlift_question OWNER TO postgres; -- -- Name: COLUMN brandlift_question.id; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.brandlift_question.id IS '(DC2Type:uuid)'; -- -- Name: COLUMN brandlift_question.campaign_id; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.brandlift_question.campaign_id IS '(DC2Type:uuid)'; -- -- Name: brandlift_respondent; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.brandlift_respondent ( id integer NOT NULL, campaign_id uuid NOT NULL, segment_id integer NOT NULL, aam_uuid character varying(255) DEFAULT NULL::character varying, "timestamp" timestamp(0) without time zone NOT NULL ); ALTER TABLE public.brandlift_respondent OWNER TO postgres; -- -- Name: COLUMN brandlift_respondent.campaign_id; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.brandlift_respondent.campaign_id IS '(DC2Type:uuid)'; -- -- Name: brandlift_respondent_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.brandlift_respondent_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.brandlift_respondent_id_seq OWNER TO postgres; -- -- Name: brandlift_segment; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.brandlift_segment ( id integer NOT NULL, name character varying(255) NOT NULL ); ALTER TABLE public.brandlift_segment OWNER TO postgres; -- -- Name: brandlift_segment_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.brandlift_segment_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.brandlift_segment_id_seq OWNER TO postgres; -- -- Name: campaign; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.campaign ( id uuid NOT NULL, format_id integer NOT NULL, advertiser character varying(255) NOT NULL, name character varying(255) NOT NULL, date_start timestamp(0) without time zone NOT NULL, date_end timestamp(0) without time zone NOT NULL ); ALTER TABLE public.campaign OWNER TO postgres; -- -- Name: COLUMN campaign.id; Type: COMMENT; Schema: public; Owner: postgres -- COMMENT ON COLUMN public.campaign.id IS '(DC2Type:uuid)'; -- -- Name: doctrine_migration_versions; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.doctrine_migration_versions ( version character varying(191) NOT NULL, executed_at timestamp(0) without time zone DEFAULT NULL::timestamp without time zone, execution_time integer ); ALTER TABLE public.doctrine_migration_versions OWNER TO postgres; -- -- Name: format; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.format ( id integer NOT NULL, name character varying(255) NOT NULL ); ALTER TABLE public.format OWNER TO postgres; -- -- Name: format_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.format_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public.format_id_seq OWNER TO postgres; -- -- Data for Name: brandlift; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.brandlift (campaign_id, respondent_goal) FROM stdin; 8d8c79ce-188c-4f5e-bd72-edb854faf34c   500 \. -- -- Data for Name: brandlift_answer; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.brandlift_answer (id, question_id, respondent_id) FROM stdin; 28 6d6596f4-9418-4f76-9234-c3c943ca56cf    18 29   c84760de-be6f-4f8e-be32-9195846307cf    18 30   6d6596f4-9418-4f76-9234-c3c943ca56cf    19 31   c84760de-be6f-4f8e-be32-9195846307cf    19 32   6d6596f4-9418-4f76-9234-c3c943ca56cf    20 33   c84760de-be6f-4f8e-be32-9195846307cf    20 34   6d6596f4-9418-4f76-9234-c3c943ca56cf    21 35   c84760de-be6f-4f8e-be32-9195846307cf    21 36   6d6596f4-9418-4f76-9234-c3c943ca56cf    22 37   c84760de-be6f-4f8e-be32-9195846307cf    22 \. -- -- Data for Name: brandlift_answer_content; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.brandlift_answer_content (id, answer_id, brand_id) FROM stdin; 54 28  5 55    28  7 56    29  5 57    29  8 58    30  5 59    30  7 60    31  7 61    31  5 62    32  5 63    32  7 64    33  6 65    33  7 66    33  8 67    34  7 68    34  5 69    35  6 70    35  7 71    35  8 72    36  7 73    36  5 74    37  6 75    37  7 \. -- -- Data for Name: brandlift_brand; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.brandlift_brand (id, campaign_id, name) FROM stdin; 5   8d8c79ce-188c-4f5e-bd72-edb854faf34c    Nike 6  8d8c79ce-188c-4f5e-bd72-edb854faf34c    Adidas 7    8d8c79ce-188c-4f5e-bd72-edb854faf34c    Lacoste 8   8d8c79ce-188c-4f5e-bd72-edb854faf34c    Puma \. -- -- Data for Name: brandlift_question; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.brandlift_question (id, campaign_id, title) FROM stdin; 6d6596f4-9418-4f76-9234-c3c943ca56cf  8d8c79ce-188c-4f5e-bd72-edb854faf34c    Parmi les propositions suivantes, pour lesquelles avez-vous vu de la publicité vidéo en ligne au cours du dernier mois ? c84760de-be6f-4f8e-be32-9195846307cf   8d8c79ce-188c-4f5e-bd72-edb854faf34c    Si vous deviez prochainement choisir une marque de streetwear la ou lesquelles choisiriez-vous ? \. -- -- Data for Name: brandlift_respondent; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.brandlift_respondent (id, campaign_id, segment_id, aam_uuid, "timestamp") FROM stdin; 10    8d8c79ce-188c-4f5e-bd72-edb854faf34c    3   912407327014    2021-02-01 18:04:43 11  8d8c79ce-188c-4f5e-bd72-edb854faf34c    3   2692416912404   2021-02-01 18:06:01 12  8d8c79ce-188c-4f5e-bd72-edb854faf34c    4   9741094120421   2021-02-01 18:06:56 13  8d8c79ce-188c-4f5e-bd72-edb854faf34c    4   8973093247093   2021-02-01 18:07:59 14  8d8c79ce-188c-4f5e-bd72-edb854faf34c    4   2147124472178421    2021-02-01 18:08:34 15  8d8c79ce-188c-4f5e-bd72-edb854faf34c    4   2147124472178421    2021-02-01 18:09:20 16  8d8c79ce-188c-4f5e-bd72-edb854faf34c    4   23253532532352  2021-02-01 18:09:53 17  8d8c79ce-188c-4f5e-bd72-edb854faf34c    3   357325732577352 2021-02-01 18:20:01 18  8d8c79ce-188c-4f5e-bd72-edb854faf34c    3   357325732577352 2021-02-01 18:21:12 19  8d8c79ce-188c-4f5e-bd72-edb854faf34c    3   4354646464223   2021-02-01 18:22:14 20  8d8c79ce-188c-4f5e-bd72-edb854faf34c    4   6855985895808   2021-02-01 18:25:41 21  8d8c79ce-188c-4f5e-bd72-edb854faf34c    4   6855985895808   2021-02-01 18:26:03 22  8d8c79ce-188c-4f5e-bd72-edb854faf34c    4   6855985895808   2021-02-01 18:26:33 \. -- -- Data for Name: brandlift_segment; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.brandlift_segment (id, name) FROM stdin; 3  expo 4  no expo \. -- -- Data for Name: campaign; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.campaign (id, format_id, advertiser, name, date_start, date_end) FROM stdin; 8d8c79ce-188c-4f5e-bd72-edb854faf34c    2   Nike    Air Max 270 2021-01-25 00:00:00 2021-02-28 00:00:00 \. -- -- Data for Name: doctrine_migration_versions; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.doctrine_migration_versions (version, executed_at, execution_time) FROM stdin; DoctrineMigrations\\Version20210131122327  2021-01-31 13:23:34 199 \. -- -- Data for Name: format; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.format (id, name) FROM stdin; 2    Brandlift \. -- -- Name: brandlift_answer_content_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.brandlift_answer_content_id_seq', 75, true); -- -- Name: brandlift_answer_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.brandlift_answer_id_seq', 37, true); -- -- Name: brandlift_brand_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.brandlift_brand_id_seq', 8, true); -- -- Name: brandlift_respondent_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.brandlift_respondent_id_seq', 22, true); -- -- Name: brandlift_segment_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.brandlift_segment_id_seq', 4, true); -- -- Name: format_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.format_id_seq', 2, true); -- -- Name: brandlift_answer_content brandlift_answer_content_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_answer_content ADD CONSTRAINT brandlift_answer_content_pkey PRIMARY KEY (id); -- -- Name: brandlift_answer brandlift_answer_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_answer ADD CONSTRAINT brandlift_answer_pkey PRIMARY KEY (id); -- -- Name: brandlift_brand brandlift_brand_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_brand ADD CONSTRAINT brandlift_brand_pkey PRIMARY KEY (id); -- -- Name: brandlift brandlift_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift ADD CONSTRAINT brandlift_pkey PRIMARY KEY (campaign_id); -- -- Name: brandlift_question brandlift_question_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_question ADD CONSTRAINT brandlift_question_pkey PRIMARY KEY (id); -- -- Name: brandlift_respondent brandlift_respondent_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_respondent ADD CONSTRAINT brandlift_respondent_pkey PRIMARY KEY (id); -- -- Name: brandlift_segment brandlift_segment_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_segment ADD CONSTRAINT brandlift_segment_pkey PRIMARY KEY (id); -- -- Name: campaign campaign_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.campaign ADD CONSTRAINT campaign_pkey PRIMARY KEY (id); -- -- Name: doctrine_migration_versions doctrine_migration_versions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.doctrine_migration_versions ADD CONSTRAINT doctrine_migration_versions_pkey PRIMARY KEY (version); -- -- Name: format format_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.format ADD CONSTRAINT format_pkey PRIMARY KEY (id); -- -- Name: answer_content_unique; Type: INDEX; Schema: public; Owner: postgres -- CREATE UNIQUE INDEX answer_content_unique ON public.brandlift_answer_content USING btree (answer_id, brand_id); -- -- Name: answer_unique; Type: INDEX; Schema: public; Owner: postgres -- CREATE UNIQUE INDEX answer_unique ON public.brandlift_answer USING btree (question_id, respondent_id); -- -- Name: idx_1f1512ddd629f605; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_1f1512ddd629f605 ON public.campaign USING btree (format_id); -- -- Name: idx_3be90c3a44f5d008; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_3be90c3a44f5d008 ON public.brandlift_answer_content USING btree (brand_id); -- -- Name: idx_3be90c3aaa334807; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_3be90c3aaa334807 ON public.brandlift_answer_content USING btree (answer_id); -- -- Name: idx_80d627bdf639f774; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_80d627bdf639f774 ON public.brandlift_question USING btree (campaign_id); -- -- Name: idx_d5009950db296aad; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_d5009950db296aad ON public.brandlift_respondent USING btree (segment_id); -- -- Name: idx_d5009950f639f774; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_d5009950f639f774 ON public.brandlift_respondent USING btree (campaign_id); -- -- Name: idx_d671e368f639f774; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_d671e368f639f774 ON public.brandlift_brand USING btree (campaign_id); -- -- Name: idx_fcce59931e27f6bf; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_fcce59931e27f6bf ON public.brandlift_answer USING btree (question_id); -- -- Name: idx_fcce5993ce80cd19; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_fcce5993ce80cd19 ON public.brandlift_answer USING btree (respondent_id); -- -- Name: campaign fk_1f1512ddd629f605; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.campaign ADD CONSTRAINT fk_1f1512ddd629f605 FOREIGN KEY (format_id) REFERENCES public.format(id); -- -- Name: brandlift_answer_content fk_3be90c3a44f5d008; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_answer_content ADD CONSTRAINT fk_3be90c3a44f5d008 FOREIGN KEY (brand_id) REFERENCES public.brandlift_brand(id); -- -- Name: brandlift_answer_content fk_3be90c3aaa334807; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_answer_content ADD CONSTRAINT fk_3be90c3aaa334807 FOREIGN KEY (answer_id) REFERENCES public.brandlift_answer(id); -- -- Name: brandlift fk_5b5d1287f639f774; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift ADD CONSTRAINT fk_5b5d1287f639f774 FOREIGN KEY (campaign_id) REFERENCES public.campaign(id); -- -- Name: brandlift_question fk_80d627bdf639f774; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_question ADD CONSTRAINT fk_80d627bdf639f774 FOREIGN KEY (campaign_id) REFERENCES public.brandlift(campaign_id); -- -- Name: brandlift_respondent fk_d5009950db296aad; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_respondent ADD CONSTRAINT fk_d5009950db296aad FOREIGN KEY (segment_id) REFERENCES public.brandlift_segment(id); -- -- Name: brandlift_respondent fk_d5009950f639f774; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_respondent ADD CONSTRAINT fk_d5009950f639f774 FOREIGN KEY (campaign_id) REFERENCES public.brandlift(campaign_id); -- -- Name: brandlift_brand fk_d671e368f639f774; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_brand ADD CONSTRAINT fk_d671e368f639f774 FOREIGN KEY (campaign_id) REFERENCES public.brandlift(campaign_id); -- -- Name: brandlift_answer fk_fcce59931e27f6bf; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_answer ADD CONSTRAINT fk_fcce59931e27f6bf FOREIGN KEY (question_id) REFERENCES public.brandlift_question(id); -- -- Name: brandlift_answer fk_fcce5993ce80cd19; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.brandlift_answer ADD CONSTRAINT fk_fcce5993ce80cd19 FOREIGN KEY (respondent_id) REFERENCES public.brandlift_respondent(id); -- -- PostgreSQL database dump complete --

所需的输出::
campaign_id => 8d8c79ce-188c-4f5e-bd72-edb854faf34c
旗舰品牌" =>耐克(id = 5)

Desired output with :
campaign_id => 8d8c79ce-188c-4f5e-bd72-edb854faf34c
"flagship brand" => Nike (id=5)

<身体>
brand_name segment_name 计数
阿迪达斯 expo 0
阿迪达斯 没有博览会 3
Lacoste expo 1
Lacoste 没有博览会 3
耐克 expo 2
耐克 没有博览会 0
彪马 expo 1
彪马 没有博览会 2
brand_name segment_name count
Adidas expo 0
Adidas no expo 3
Lacoste expo 1
Lacoste no expo 3
Nike expo 2
Nike no expo 0
Puma expo 1
Puma no expo 2

更新:
最终在 brandlift_question 表中添加了常规"列，这样以后如果我必须在Brandlift中添加更多问题，就可以根据其他问题索引而不是仅在第一和第二索引之间比较答案.第二

UPDATE :
Ended up adding an 'ordinal' column to brandlift_question table, so that later if I ever have to add more questions in a Brandlift, I can compare answers based on other questions indexes and not just between first & second

SELECT
    bb.name AS brand_name,
    s.name AS segment_name,
    COUNT(q2ac.brand_id)
FROM
    brandlift b
    LEFT JOIN brandlift_brand bb ON b.campaign_id = bb.campaign_id
    LEFT JOIN brandlift_respondent r ON b.campaign_id = r.campaign_id
    LEFT JOIN brandlift_segment s ON s.id = r.segment_id
    
    LEFT JOIN brandlift_question q1 ON b.campaign_id = q1.campaign_id AND q1.ordinal = 1
    LEFT JOIN brandlift_answer q1a ON r.id = q1a.respondent_id AND q1.id = q1a.question_id
    INNER JOIN brandlift_answer_content q1ac ON  q1a.id = q1ac.answer_id AND q1ac.brand_id = 5 -- the "flagship brand" id
    
    LEFT JOIN brandlift_question q2 ON q1.campaign_id = q2.campaign_id AND q2.ordinal = 2
    INNER JOIN brandlift_answer q2a ON q1a.respondent_id = q2a.respondent_id AND q2.id = q2a.question_id
    LEFT JOIN brandlift_answer_content q2ac ON q2a.id = q2ac.answer_id AND bb.id = q2ac.brand_id
WHERE
    b.campaign_id = '8d8c79ce-188c-4f5e-bd72-edb854faf34c'
GROUP BY
    bb.name,
    s.name

但是还有一点，如果某个段上没有响应者，则此查询的结果输出将不包含该段值填充为0的行.如果可能，也需要它们

But there is still a point, if there is no respondent on a segment, the result output of this query will not contain rows with that segment values filled with 0. Need them too if possible

我很想提建议

推荐答案

如果您不想执行mod，则需要选择questionID. 并且我使用了交叉连接来获取其他一些没有响应的细分.在select语句中，您 case 语句将segmentName计为0.

If you don't want to do mod, then you need to select questionID instead. and I used cross join to get some other segment where not in respond. and in select statement, you case statement to count segmentName to 0.

SELECT
    bb.name,
    s.name,
    CASE WHEN s.id IN(
        SELECT
            r.segment_id FROM brandlift_respondent r) THEN
        COUNT(q2ac.brand_id)
    ELSE
        0
    END AS CountNumber
FROM
    brandlift b
    LEFT JOIN brandlift_brand bb ON b.campaign_id = bb.campaign_id
    LEFT JOIN brandlift_respondent r ON b.campaign_id = r.campaign_id
    
    CROSS JOIN brandlift_segment s
    
    LEFT JOIN brandlift_question q1 ON b.campaign_id = q1.campaign_id AND q1.ordinal = 1
    LEFT JOIN brandlift_answer q1a ON r.id = q1a.respondent_id AND q1.id = q1a.question_id
    INNER JOIN brandlift_answer_content q1ac ON q1a.id = q1ac.answer_id AND q1ac.brand_id = 1 -- the "flagship brand" id
        
    LEFT JOIN brandlift_question q2 ON q1.campaign_id = q2.campaign_id AND q2.ordinal = 2
    LEFT JOIN brandlift_answer q2a ON q1a.respondent_id = q2a.respondent_id AND q2.id = q2a.question_id
    LEFT JOIN brandlift_answer_content q2ac ON q2a.id = q2ac.answer_id AND bb.id = q2ac.brand_id
WHERE
    b.campaign_id = :campaign_id -- parameter
    AND R.segment_id = s.id OR s.id NOT IN(SELECT r.segment_id FROM brandlift_respondent r)
GROUP BY
    bb.name,
    s.name,
    s.id

结果显示像这样

这篇关于根据上一个问题的答案选择MCQ答案计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆