http://blog.dengqinghua.net

SELECT * FROM deals
WHERE
    isHongPiPiWuyou = 1
  AND
    isSingleOnSale = 1
  AND
    isZhuanchangPromoted = 1
  AND
    isTodayNew = 1

我想要打一个超级男装单件优惠标, 标的定义如下:

(商品是秒杀 而且 分类是男装) 或者 (商品为单件优惠 而且 剩余库存 在 10 到 20 之间)

public class SqlRunnerTest {
    // 使用一个Database对象来存储这些数据
    public static class Database {
        public Seller[] sellers;
        public Product[] products;
    }

    public static class Seller {
        public String name;
        public int id;

        public Seller(String name, int id) {
            this.name = name;
            this.id   = id;
        }
    }

    public static class Product {
        public int id, sellerId;

        public Product(int id, int sellerId) {
            this.id       = id;
            this.sellerId = sellerId;
        }
    }
}

public class SqlRunnerTest {
    static Database database;
    static SqlRunner sqlRunner;
    static String sql;

    @BeforeClass public static void setUp() {
        Seller[] sellers = {
                new Seller("dengqinghua", 1),
                new Seller("kimiGao", 2),
                new Seller("DS", 3),
        };

        Product[] products = {
                new Product(1024, 1),
                new Product(1025, 2),
                new Product(1026, 3),
        };

        database = new Database();
        database.products = products;
        database.sellers  = sellers;
    }

}

public class SqlRunnerTest {
    static Database database;
    static SqlRunner sqlRunner;
    static String sql;

    @Test public void run() throws Exception {
        sqlRunner = new SqlRunner("merchant_system", database);
        sql = "SELECT \n"
                + "\"products\".\"id\", \"sellers\".\"name\" \n"
                + "FROM \n"
                + " \"merchant_system\".\"products\" \n"
                + "INNER JOIN \n"
                + " \"merchant_system\".\"sellers\" \n"
                + "ON \n"
                + " \"merchant_system\".\"sellers\".\"id\" = \"merchant_system\".\"products\".\"sellerId\" \n"
                + "WHERE \n"
                + " \"sellers\".\"name\" = 'dengqinghua'";

        Result<Record> result = sqlRunner.run(sql);
        assertThat(result.format(), is(
                "+----+-----------+" + "\n" +
                "|  id|name       |" + "\n" +
                "+----+-----------+" + "\n" +
                "|1024|dengqinghua|" + "\n" +
                "+----+-----------+"
                ));
    }
}

我想要打一个超级男装单件优惠标, 标的定义如下:

(商品是秒杀 而且 分类是男装) 或者 (商品为单件优惠 而且 剩余库存 在 10 到 20 之间)

SELECT
  *
FROM
  badge_views
WHERE
    (isMiaosha = 1 AND inputTag = '男装')
  OR
    (isSingeSale =1 AND rangeStock BETEEN (10, 20))

我想要打一个超级男装单件优惠标, 标的定义如下:

(商品是秒杀 而且 分类是男装) 或者 (商品为单件优惠 而且 剩余库存 在 10 到 20 之间),

该角标只给男用户展示, 新老用户都可以看到, 所有页面都生效, 来源只作用于PC端

角标id    2
角标文案  超级男装单件优惠标
角标图片  XXX图片
开始时间  1月1日
结束时间  1月5日

基础规则
1. 客户端类型 client_type: PC
2. 命中的页面 page_lists: 所有页面
3. 新老用户 user_type: 所有用户
4. 用户角色 user_role: 男用户

SELECT
  *
FROM
  badge_views
WHERE
    (isMiaosha = 1 AND inputTag = '男装')
  OR
    (isSingeSale =1 AND rangeStock BETEEN (10, 20))

角标1 用户角色: 女
角标2 超级男装单件优惠标
角标3 用户角色: 男

SELECT
  *
FROM
  badge_views
WHERE
    (isMiaosha = 1 AND inputTag = '男装')
  OR
    (isSingeSale = 1 AND rangeStock BETEEN (10, 20))

<SqlAnalyzer>
    <!--是否为秒杀活动-->
    <Entry>
        <field>isMiaosha</field>
        <type>boolean</type>
        <sql>
            <!--真实业务系统中的数据结构-->
            SELECT 1 FROM products WHERE isSeckill = 1
        </sql>
    </Entry>

    <!--分类名称-->
    <Entry>
        <field>inputTag</field>
        <type>input</type>
        <sql>
            SELECT products.tagName FROM products
        </sql>
    </Entry>

    <!--是否单件优惠-->
    <Entry>
        <field>isSingeSale</field>
        <type>boolean</type>
        <sql>
            SELECT 1 FROM discountProducts WHERE discounts.conditionPurchaseCount = 1
        </sql>
    </Entry>

    <!--库存范围-->
    <Entry>
        <field>rangeStock</field>
        <type>range</type>
        <sql>
            SELECT product_stocks.count FROM product_stocks
        </sql>
    </Entry>
</SqlAnalyzer>

isSeckill: 1,
tagName: "男装"
conditionPurchaseCount: 1
count: 15

SELECT
  *
FROM
  badge_views
WHERE
    (isMiaosha = 1 AND inputTag = '男装')
  OR
    (isSingeSale = 1 AND rangeStock BETEEN (10, 20))

public class BadgeView {
  public int isMiaosha,
    isSingeSale,
    rangeStock;

  public String inputTag;
}

badgeView = new BadgeView();

badgeView.isMiaosha = 1
badgeView.isSingeSale = 1
badgeView.rangeStock = 15
badgeView.inputTag = "男装"

SELECT
  *
FROM
  badge_views
WHERE
    (isMiaosha = 1 AND inputTag = '男装')
  OR
    (isSingeSale = 1 AND rangeStock BETEEN (10, 20))

单条SQL的执行时间

public class SqlRunnerTest {
    static Database database;
    static SqlRunner sqlRunner;
    static String sql;

    @Test public void run() throws Exception {
        sqlRunner = new SqlRunner("merchant_system", database);
        sql = "SELECT \n"
                + "\"products\".\"id\", \"sellers\".\"name\" \n"
                + "FROM \"merchant_system\".\"products\" \n"
                + "INNER JOIN \"merchant_system\".\"sellers\" \n"
                + "ON \"merchant_system\".\"sellers\".\"id\" = \"merchant_system\".\"products\".\"sellerId\" \n"
                + "WHERE \"sellers\".\"name\" = 'dengqinghua'";

        Result<Record> result = sqlRunner.run(sql);
        assertThat(result.format(), is(
                "+----+-----------+" + "\n" +
                "|  id|name       |" + "\n" +
                "+----+-----------+" + "\n" +
                "|1024|dengqinghua|" + "\n" +
                "+----+-----------+"
                ));
    }
}

mvn test -Dtest=SqlRunnerTest

[INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 4.23 s - in com.dengqinghua.calcite.SqlRunnerTest

public class SqlRunner {
        private CalciteConnection initConnection() {
        try {
            Connection connection = DriverManager.getConnection("jdbc:calcite:", new Properties());
            return connection.unwrap(CalciteConnection.class);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException();
        }
    }
}

static final int POOL_SIZE = Runtime.getRuntime().availableProcessors() * 2 + 1;
static Connection[POOL_SIZE] connectionPools;

static {
    IntStream.range(0, POOL_SIZE).forEach(i -> {
          connectionPools[i] = createConnection;
    })
}

public class PreparedStatement {
    public static void prepare(String[] sqls) {
        for(sql : sqls) {
            // 在项目启动的时候, 就执行一下所有可能的sql
            prepare(sql);
        }
    }
}

SELECT
    *
FROM
    badge_views
WHERE
      isMiaosha = 1
    AND
      inputTag = '男装'
    AND
      isSingeSale = 1
    AND
      rangeStock BETEEN (10, 20)

public class Product {
  public int isSeckill;
  public String tagName;
}

源数据 -> 视图数据 -> 源数据 -> 视图数据 -> ...

SELECT
    ROUND(COALESCE(MIN(p), 0)/100.0, 2)
FROM (
        SELECT
            CAST(discountRules.price - discountRules.savedAmount AS DECIMAL(10, 2))
             AS p
        FROM
            input_view.discountRules
        WHERE
            (discountRules.price >= discountRules.conditionMoney AND discountRules.type = 0 AND discountRules.conditionMoney > 0)
            OR
            (discountRules.conditionPurchaseCount = 1 AND discountRules.type = 1)
    UNION
        SELECT
            CAST((discountRules.price) * (1 - discountRules.savedPercent/10000.0) AS DECIMAL(10, 2))
            AS p
        FROM
            input_view.discountRules
        WHERE
            (discountRules.price >= discountRules.conditionMoney AND discountRules.type = 0 AND discountRules.conditionMoney > 0)
            OR
            (discountRules.conditionPurchaseCount = 1 AND discountRules.type = 1)
    UNION
        SELECT
            CAST((discountRules.price - COALESCE(discountRules.price/NULLIF(input_view.discountRules.conditionMoney, 0), 0) * discountRules.noLimit * discountRules.savedAmount) AS DECIMAL(10, 2))
             AS p
        FROM
            input_view.discountRules
        WHERE
            (discountRules.price >= discountRules.conditionMoney AND discountRules.type = 0 AND discountRules.conditionMoney > 0)
            OR
            (discountRules.conditionPurchaseCount = 1 AND discountRules.type = 1)
) t

/**
 * 防止SQL写得不对, 导致直接报错, 所以在这里会跑这个测试
 *
 * <p>
 * 在上线之前务必执行
 *
 * <pre>
 *      mvn -Dtest="SqlAnalyzerTest#testEverySQLRunnable" test
 * </pre>
 *
 * @throws Exception 如果SQL写得不对, 在这儿测试这儿会报错
 */
@Test public void testEverySQLRunnable() throws Exception {
    // 这个地方设置了如果执行SQL报错, 就会抛出异常
    Helper.setPrivateStaticField(SqlRunner.class, "isThrowExceptionWhenRunSqlFailed", true);

    Field field = SqlRunner.class.getDeclaredField("PREPARE_VIEWS");
    field.setAccessible(true);
    SchemaView[] prepareViews = (SchemaView[]) field.get(SqlRunner.class);

    for (SchemaView bean : prepareViews) {
        List<String> sqlList = bean.allPossibleSqls();

        // 执行所有SQL
        sqlList.forEach(sql -> SqlRunner.run(bean, sql));
    }
}