代码之家 › 专栏 › 技术社区 › Abraham P

postgres中的嵌套分区

data-partitioning unique-constraint window-functions postgresql sql

0

Abraham P · 技术社区 · 8 年前

我正在尝试创建引用代码基础结构。我想使用最多12个字符的用户的电子邮件,重复与整数删除:

给三个用户发邮件 bob@gmail.com , bob@hotmail.com ,和 bob@yahoo.com ,我想生成代码 bob , bob1 ,和 bob2 .

我使用以下sql完成了此操作:

UPDATE user_referral_codes urc
    SET referral_code = (
      LEFT(LEFT(email, strpos(email, '@') - 1), 12)
      || (CASE WHEN seqnum > 1 THEN (seqnum-1)::TEXT ELSE '' END)
    )
    FROM (
      SELECT
       users.*,
        row_number() OVER (
          PARTITION BY LEFT(LEFT(email, strpos(email, '@') - 1), 12)
          ORDER BY id
        ) AS seqnum
      FROM users
    ) AS u
  WHERE urc.user_id = u.id;

这很好,但是有两种情况:

1) 引用代码必须是全局唯一的,因此添加用户 bob1@gmail.com 和 bob2@gmail.com 混合后应产生以下配对:

bob@gmail.com | bob
bob@hotmail.com | bob3
bob@yahoo.com | bob4
bob1@gmail.com | bob1
bob2@gmail.com | bob2

我怎么解释?

2) 还有第二个代码,优惠券代码。如何确保此表不重复?

e、 g.如果有优惠券代码 bob2型 是存在的吗 邮箱:bob2@gmail.com 也应该得到推荐码 bob21 ?

示例架构:

    CREATE TABLE users(
  id                    bigint                      NOT NULL PRIMARY KEY,
  email                 TEXT                        NOT NULL UNIQUE
 );

CREATE TABLE user_referral_codes(
  user_id                 bigint          NOT NULL REFERENCES users (id),
  referral_code           TEXT
);

CREATE TABLE coupon_codes(
  code_id                 TEXT
);

INSERT INTO users(id, email) VALUES(1, 'bob@flockcover.com');
INSERT INTO users(id, email) VALUES(2, 'bob@google.com');
INSERT INTO users(id, email) VALUES(3, 'bob@gmail.com');
INSERT INTO users(id, email) VALUES(4, 'bob1@gmail.com');

INSERT INTO user_referral_codes(user_id) VALUES(1);
INSERT INTO user_referral_codes(user_id) VALUES(2);
INSERT INTO user_referral_codes(user_id) VALUES(3);
INSERT INTO user_referral_codes(user_id) VALUES(4);

INSERT INTO coupon_codes(code_id) VALUES('bob2');

示例select查询演示当前(不完整)行为:

SELECT LEFT(LEFT(email, strpos(email, '@') - 1), 12)
  || (CASE WHEN seqnum > 1 THEN (seqnum-1)::TEXT ELSE '' END)
  FROM (
    SELECT
      users.*,
      row_number() OVER (
        PARTITION BY LEFT(LEFT(email, strpos(email, '@') - 1), 12)
        ORDER BY id
      ) AS seqnum
    FROM users
  ) as u;

sqlfiddle链接演示问题: http://sqlfiddle.com/#!17/0a6ea

0 回复 | 直到 8 年前